CODEWITHSUNDEEP
oracle-databasesqlplus
H_B
H_B

Reputation: 37

CURRENT_DATE Oracle

I'm having hard time to add a new colomn of date of birth with the check rule age between 18 and 65. I'm using sqplus with Oracle Alway getting the error message ORA00920 Need your help please

ALTER TABLE Vendeur ADD (dateNaissance DATE, 
            dateDebutProjet DATE NOT NULL,  
            DateFinProjet DATE NOT NULL,
            CONSTRAINT chk_date_Birth CHECK ((TRUNC(CURRENT_DATE)-dateNaissance) 
            BETWEEN 18 AND 65),
            CONSTRAINT chk_date_Projet CHECK (DateFinProjet > dateDebutProjet));

Upvotes: 1

Views: 598

Answers (2)

H_B
H_B

Reputation: 37

if it can help, the solution without triggers (since we didn't learn hem at that time):

ALTER TABLE Vendeur ADD (dateNaissance DATE, debutProjet DATE DEFAULT '01/01/1000' NOT NULL, finProjet DATE DEFAULT '02/01/1000' NOT NULL, dateDuJour Date DEFAULT CURRENT_DATE, CONSTRAINT chk_date_Projet CHECK (finProjet > debutProjet), CONSTRAINT chk_date_Birth CHECK ((dateDuJour - dateNaissance)\365 BETWEEN 18 AND 65) );

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36922

Check constraints cannot call non-deterministic functions like CURRENT_DATE. Check constraints are supposed to always be true, weird things might happen if check constraints aged out.

The below sample code shows one of the errors you might get trying to use CURRENT_DATE in a check constraint:

SQL> create table test1(a date);

Table created.

SQL> alter table test1 add constraint test1_ck1 check(a > date '2000-01-01');

Table altered.

SQL> alter table test1 add constraint test1_ck2 check(a > current_date);
alter table test1 add constraint test1_ck2 check(a > current_date)
                                                     *
ERROR at line 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint

Create a trigger to workaround this problem:

create or replace trigger test1_date_gt_today
before update or insert of a on test1
for each row
begin
    if :new.a is null or :new.a < current_date then
        raise_application_error(-20000, 'The date cannot be earlier than today.');
    end if;
end;
/

Below is an example of one INSERT working, and one failing to meet the condition in the trigger:

SQL> insert into test1 values(sysdate + 1);

1 row created.

SQL> insert into test1 values(sysdate - 1);
insert into test1 values(sysdate - 1)
            *
ERROR at line 1:
ORA-20000: The date cannot be earlier than today.
ORA-06512: at "JHELLER.TEST1_DATE_GT_TODAY", line 3
ORA-04088: error during execution of trigger 'JHELLER.TEST1_DATE_GT_TODAY'

Upvotes: 0

Related Questions