Re: Hair-Puller - Date constraint

From: Anurag Varma <avoracle_at_gmail.com>
Date: 26 Jan 2006 09:38:29 -0800
Message-ID: <1138297109.515301.234870@g44g2000cwa.googlegroups.com>

Although you can do this using a trigger... another way can be:

ORA92> create table t1 (a number, b date, c date default sysdate); Table created.

ORA92> alter table t1 add constraint t1_dob_cons check (b > to_date('01-01-1900','mm-dd-yyyy') and b < c);

Table altered.

ORA92> insert into t1 (a,b) values (1,to_date('01-26-2006 12:34:00','mm-dd-yyyy hh24:mi:ss'));
insert into t1 (a,b) values (1,to_date('01-26-2006 12:34:00','mm-dd-yyyy hh24:mi:ss'))
*

ERROR at line 1:
ORA-02290: check constraint (AVARMA.T1_DOB_CONS) violated

ORA92> insert into t1 (a,b) values (1,to_date('01-26-2006 12:30:00','mm-dd-yyyy hh24:mi:ss'));

1 row created.

ORA92> select * from t1;

         A B                        C
---------- ------------------------ ------------------------
         1 26-JAN-06 12:30:00       26-JAN-06 12:32:54

ORA92> insert into t1 (a,b) values
(1,to_date('01-26-1800','mm-dd-yyyy'));
insert into t1 (a,b) values (1,to_date('01-26-1800','mm-dd-yyyy'))
*

ERROR at line 1:
ORA-02290: check constraint (AVARMA.T1_DOB_CONS) violated

Anurag Received on Thu Jan 26 2006 - 11:38:29 CST