CODEWITHSUNDEEP
sqloracle-database
Azeez Sayed
Azeez Sayed

Reputation: 1

how to add check constraints for date columns

I am getting this error

ORA-02438: Column check constraint cannot reference other columns

when I am performing this query

alter table Issue
modify Issue_Date not null check (Issue_Date <= sys_date);

as well as I have to add this condition also (issue_date<return_date);

and when I tried this 

alter table Issue
add constraint ck_Issue_Date not null check (Issue_Date <= sys_date);

ERROR ORA-00904: : invalid identifier

Upvotes: 0

Views: 2480

Answers (2)

Rusty
Rusty

Reputation: 2138

You tried to mix up inline column level constraint and table level constraint (for more than 1 column). Please simply split them on 2 statements:

alter table Issue
  modify Issue_Date not null;

alter table Issue
  add constraint ck_Issue_Date check (Issue_Date <= sys_date);

alter table Issue
  add constraint ck_Issue_Date2 check (issue_date<return_date);

Upvotes: 0

spencer7593
spencer7593

Reputation: 108430

I suspect you are wanting to reference the Oracle SYSDATE function, not a column named sys_date.

Unfortunately, the conditions in a CHECK CONSTRAINT cannot reference the SYSDATE function.

To get the database enforce this type of restriction on the value of a column, that would require a TRIGGER.

For example, something like this:

CREATE OR REPLACE TRIGGER trg_issue_issue_date_biu
BEFORE INSERT OR UPDATE ON Issue
FOR EACH ROW
BEGIN
   IF (NEW.Issue_Date <= SYSDATE) THEN
      NULL;
   ELSE
      RAISE_APPLICATION_ERROR(-20000, 'Invalid: Issue_Date is NULL or >SYSDATE');
   END IF;
END;

Upvotes: 2

Related Questions