RE: Unique Key enforcement on a DATE Column for DAY (mm/dd/yy)

Create a function based unique index/constraint. Didn't test this in 8i though.

create table td1 (d1 date);
create unique index td1_i1 on td1 (trunc(d1) );

insert into td1 select sysdate from dual; 1 row created.

insert into td1 select sysdate from dual *
ERROR at line 1:
ORA-00001: unique constraint (TD1_I1) violated

SQL> insert into td1 select sysdate+1 from dual;

1 row created.

SQL> /
insert into td1 select sysdate+1 from dual *
ERROR at line 1:
ORA-00001: unique constraint (TD1_I1) violated

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Seiler Sent: Tuesday, October 23, 2007 10:17 AM To: bnsarma_at_gmail.com
Cc: oracle_L_list
Subject: Re: Unique Key enforcement on a DATE Column for DAY (mm/dd/yy)

Umm ... just throwing this out and hope someone does all the heavy lifting? (Like I should talk)

You'll probably want an update trigger to not allow updates if that date field is populated. That is one way to only "allow users to enter data only once in a day". A unique constraint won't do that. Perhaps your phrasing or my understanding of it is off though.

The update trigger should be supported back to version 8.

Don.

On 10/23/07, BN <bnsarma_at_gmail.com> wrote:
> Greetings
>
>
> I have a requirement to enforce Uniqueuness on a DATE Column for a day
only
> (mm/dd/yy(yy)
>
> Should allow users to enter data only once in a day, Can it be
enforced
> through a Unique constraint
>
> Need to implement this in Oralce 8i, 9i, 10g
>
> --
> Regards & Thanks
> BN

-- 
Don Seiler
oracle: http://ora.seiler.us
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l