RE: Unique Key enforcement on a DATE Column for DAY (mm/dd/yy)
- From: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
- To: <don@xxxxxxxxx>, <bnsarma@xxxxxxxxx>
- Date: Tue, 23 Oct 2007 10:33:09 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Don Seiler
Sent: Tuesday, October 23, 2007 10:17 AM
To: bnsarma@xxxxxxxxx
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
