在检查约束中使用日期，Oracle

Question

我试图检查添加以下约束，但Oracle返回如下所示的错误。

ALTER TABLE Table1

ADD (CONSTRAINT GT_Table1_CloseDate

CHECK (CloseDate > SYSDATE),

CONSTRAINT LT_Table1_CloseDate

CHECK (CloseDate <= SYSDATE + 365)),

CONSTRAINT GT_Table1_StartDate

CHECK (StartDate > (CloseDate + (SYSDATE + 730))));

错误：

Error report:

SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint

02436. 00000 -  "date or system variable wrongly specified in CHECK constraint"

*Cause:    An attempt was made to use a date constant or system variable,

           such as USER, in a check constraint that was not completely

           specified in a CREATE TABLE or ALTER TABLE statement.  For

           example, a date was specified without the century.

*Action:   Completely specify the date constant or system variable.

           Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",

           which a bug permitted to be created before version 8.

Answer 1

您不能在检查约束中使用SYSDATE。根据文件检查约束条件不能包含以下结构：子查询和标量子查询表达式调用不确定的函数（CURRENT_DATE，CURRENT_TIMESTAMP，DBTIMEZONE，LOCALTIMESTAMP，SESSIONTIMEZONE，SYSDATE，SYSTIMESTAMP，UID，USER和USERENV）调用用户定义的函数取消引用REF列（例如，使用DEREF函数）嵌套表列或属性伪列CURRVAL，NEXTVAL，LEVEL或ROWNUM未完全指定的日期常量对于10g第2版（10.2），请参阅约束，对于11g第2版（11.2），请参阅约束。请记住，完整性约束是关于表数据的声明，它始终为&nbsp;true。无论如何：我不确切地知道你想要实现什么，但我认为你可以使用触发器来达到这个目的。

Answer 2

将sysdate写入列并将其用于验证。此列可能是您的审核列（例如：创建日期）CREATE TABLE "AB_EMPLOYEE22"(&nbsp; &nbsp;"NAME"&nbsp; &nbsp; &nbsp;VARCHAR2 ( 20 BYTE ),&nbsp; &nbsp;"AGE"&nbsp; &nbsp; &nbsp; NUMBER,&nbsp; &nbsp;"SALARY"&nbsp; &nbsp;NUMBER,&nbsp; &nbsp;"DOB"&nbsp; &nbsp; &nbsp; DATE,&nbsp; &nbsp;"DOJ"&nbsp; &nbsp; &nbsp; DATE DEFAULT SYSDATE);Table Created&nbsp; &nbsp;&nbsp;ALTER TABLE "AB_EMPLOYEE22" ADD CONSTRAINTAGE_CHECK CHECK((ROUND((DOJ-DOB)/365)) = AGE) ENABLE;Table Altered