Re: Hair-Puller - Date constraint

From: <fitzjarrell_at_cox.net>
Date: 26 Jan 2006 09:51:15 -0800
Message-ID: <1138297875.931925.184520@g14g2000cwa.googlegroups.com>

Anurag Varma wrote:
> 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

An elegant solution should you be able to modify the table. If not, a trigger would also work:

SQL> create table dobtest (userid       varchar2(11) not null,
  2                        username  varchar2(40) not null,
  3                        dob date not null);

Table created.

SQL>
SQL> create or replace trigger dob_validate   2 before insert or update on dobtest
  3 for each row
  4 begin
  5 if :new.dob not between to_date('1900-01-01','YYYY-MM-DD') and sysdate

  6          then
  7                  raise_application_error(-20999, 'DOB is out of
acceptable range (01/01/1900 through today)');
  8          end if;

  9 end;
 10 /

Trigger created.

SQL>
SQL>
SQL> insert into dobtest

  2 values ('123-45-6789','Bink
Finklestein',to_date('1899-01-17','YYYY-MM-DD')); insert into dobtest

            *
ERROR at line 1:

ORA-20999: DOB is out of acceptable range (01/01/1900 through today)
ORA-06512: at "SCOTT.DOB_VALIDATE", line 4
ORA-04088: error during execution of trigger 'SCOTT.DOB_VALIDATE'

SQL> David Fitzjarrell Received on Thu Jan 26 2006 - 11:51:15 CST