我的Oracle 10gr2检查约束有什么问题？(What is wrong with my Oracle 10gr2 check constraint? Trying to enforce a da

我的Oracle 10gr2检查约束有什么问题？(What is wrong with my Oracle 10gr2 check constraint? Trying to enforce a date range)

我希望在日期范围内强制执行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 + 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?

最满意答案

检查约束表达式必须是确定性的，所以这种滑动日期范围在检查约束中不可强制执行。 从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)