R – wrong with the Oracle 10gr2 check constraint? Trying to enforce a date range

constraintsdatabasedateoracle

I want to enforce CHECK constraint on a date range such that all dates in column BIRTH_DATE are less than tomorrow and greater than or equal to 100 years ago. I tried this expression in a CHECK constraint:

BIRTH_DATE >= (sysdate - numtoyminterval(100, 'YEAR')) AND BIRTH_DATE < sysdate + 1

But I received the error "ORA-02436: date or system variable wrongly specified in CHECK constraint"

Is there a way to accomplish this using a CHECK constraint instead of a trigger?

Best Solution

A check constraint expression has to be deterministic, so this sort of sliding date range is not enforcable in a check constraint. From the SQL Reference

Conditions of check constraints cannot contain the following constructs:

* Subqueries and scalar subquery expressions
* Calls to the functions that are not deterministic (CURRENT_DATE,

CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)

Related Question