Re: Newbie: 'identifier must be declared' when deleting from table

"Ian McCall" <ian_at_eruvia.org> wrote in message news:_fvRa.11097$ju6.206688_at_newsfep4-glfd.server.ntli.net...
>
> "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in
> message news:LetRa.81709$H17.25850_at_sccrnsc02...
> > Should be a date. Leaving it at midnight is perfectly acceptable. Dates
> > only take 7 bytes and give you flexibility to treat them as dates.
>
> Understand your point, and I'd normally agree. The problem I'm trying to
> avoid is catching bad coding that could mess up the primary key however.
>
> With archive_date as a date a user could run insert into target_table
> values(someValue, sysdate) and it would work. sysdate now has a resolution
> including a time component, which means they could run that statement
again
> and it would work, rather than faily with a duplicate key error (the
desired
> behaviour). Enforcing varchar(8) does mean I could potentially get
problems
> like (someValue, 'herring') working instead of the YYYYMMDD I expected,
but
> at least it won't allow two values for the same day.

I'd deal with this with a check constraint

SQL> create table tab1(
  2 col1 varchar2(200),
  3 archive_date date);

Table created.

SQL> alter table tab1 add constraint pk_tab1 primary key (archive_date);

Table altered.

SQL> alter table tab1 add constraint ck_archdate check(archive_date=trunc(archive_date));

Table altered.

SQL>
SQL> insert into tab1 values('somevalue',sysdate); insert into tab1 values('somevalue',sysdate) *
ERROR at line 1:
ORA-02290: check constraint (NIALL.CK_ARCHDATE) violated

SQL>
SQL> spool off Received on Thu Jul 17 2003 - 06:19:13 CDT