What is wrong with my Oracle 10gr2 check constraint? Trying to enforce a date range

Question

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?

Answer 1

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)

Answer 2

As for why Oracle makes this restriction: check constraints must always evaluate to TRUE, even for updates. If you added a 99 year-old to the database, and then tried to update the person's email address (e.g.) in 2 year's time you would receive a check constraint violation.

What you could do, if appropriate, is have another column CREATED_DATE that defaults to SYSDATE, and make the constraint:

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

However, if you really only want to perform the check at INSERT time then do it in a database trigger or in the API code.