Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unique Key enforcement on a DATE Column for DAY (mm/dd/yy)

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

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Tue, 23 Oct 2007 11:58:38 -0400
Message-ID: <9c9b9dc90710230858u60128b24lfedbd9a06a0fcdb@mail.gmail.com>


BN,

How about just adding a unique function based index that uses a TRUNC to take the date down to a "unique" day like so:

create table rg_test( d date );
Table created.

create unique index rg_idx on rg_test ( trunc(d) ); Index created.

insert into rg_test values( sysdate );
1 row created.

 insert into rg_test values( sysdate );
insert into rg_test values( sysdate )
*
ERROR at line 1:
ORA-00001: unique constraint (U31125.RG_IDX) violated

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
>

-- 
Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 23 2007 - 10:58:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US