What is wrong with my Oracle 10gr2 control limit? Attempt to enforce date range

Question

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

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

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

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

Answer 1

The expression for the check constraint must be deterministic, so this type of slip date range cannot be used in the check constraint. From SQL Reference

:

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

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

Answer 2

, Oracle : TRUE, . 99- , (,) 2 , .

, , CREATED_DATE, SYSDATE, :

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

, INSERT, API.