How to compare date with system date in oracle 11g with check constraint?

Question

I was trying to create a constraint that checks the user input date is equal to the system date in ORACLE 11G.

CREATE TABLE  ABHISHEK(DOB DATE DEFAULT SYSDATE NOT NULL, NAME VARCHAR2(30));

This is my table structure.

ALTER TABLE ABHISHEK ADD CONSTRAINT check_dob CHECK ('DOB' = 'SELECT SYSDATE 
FROM dual');

I tried this to compare. Unfortunately, this didn't work for me.

INSERT INTO ABHISHEK (DOB, NAME) VALUES('30-APR-19','ABHI');

After executing this command, an error came showing that ORA-02290: check constraint (SYSTEM.CHECK_DOB) violated.

I expect that after executing insert command it must show one row inserted.

Answer 1

You can't use sysdate in a check constraint, as it is non-deterministic. See the documentation for the list of restrictions when creating a check constraint.

Instead, you could create a trigger, but you'd probably need to use trunc(sysdate) to compare the date to 00:00:00 at the current day, e.g.:

create trigger your_trigger
before insert or update on your_table
for each row
begin
  if trunc(:new.dob) != trunc(sysdate) then
    raise_application_error(-20001, 'DOB must be today''s date');
  end if;
end your_trigger;
/