我希望在日期范围内强制执行CHECK约束,以使列BIRTH_DATE中的所有日期都小于明天,并且大于或等于100年前。 我在CHECK约束中尝试了这个表达式:
BIRTH_DATE >= (sysdate - numtoyminterval(100, 'YEAR')) AND BIRTH_DATE < sysdate + 1但是我收到错误“ORA-02436:在CHECK约束中错误指定的日期或系统变量”
有没有办法使用CHECK约束而不是触发器来完成此操作?
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 + 1But 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?
最满意答案
检查约束表达式必须是确定性的,所以这种滑动日期范围在检查约束中不可强制执行。 从SQL参考
检查约束条件不能包含以下结构:
* 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)
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)
更多推荐
发布评论