在检查约束中使用日期，Oracle [英] Using date in a check constraint, Oracle

问题描述

 我试图添加以下约束，但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））））; 
  

错误：

 code>错误报告：
 SQL错误：ORA-02436：在CHECK约束中错误指定的日期或系统变量
 02436. 00000  - 在CHECK约束中错误指定的日期或系统变量
 *原因：试图在CREATE TABLE或ALTER TABLE语句中指定的不完全
的检查约束中使用日期常量或系统变量
（例如USER）。对于
示例，指定的日期没有世纪。 
 *操作：完全指定日期常量或系统变量。 
设置事件10149允许诸如a1> '10 -MAY-96'，
之类的约束，允许在版本8之前创建错误。
  
 
 
解决方案
不幸的是，检查约束不能引用像SYSDATE这样的函数。您需要创建一个触发器，在DML发生时检查这些值，即
  CREATE OR REPLACE TRIGGER trg_check_dates 
 BEFORE INSERT OR UPDATE ON table1 
 FOR EACH ROW 
 BEGIN 
 IF（：new.CloseDate< = SYSDATE）
 THEN 
 RAISE_APPLICATION_ERROR（-20001，
 'Invalid CloseDate：CloseDate必须大于当前日期 -  value ='|| 
 to_char（：new.CloseDate，'YYYY-MM-DD HH24：MI：SS'））; 
 END IF; 
 IF（：new.CloseDate> add_months（SYSDATE，12））
 THEN 
 RAISE_APPLICATION_ERROR（-20002，
'无效CloseDate：CloseDate必须在下一年='|| 
 to_char（：new.CloseDate，'YYYY-MM-DD HH24：MI：SS'））; 
 END IF; 
 IF（：new.StartDate< = add_months（：new.CloseDate，24））
 THEN 
 RAISE_APPLICATION_ERROR（-20002，
'无效的StartDate：StartDate必须在24个月的CloseDate  -  StartDate ='|| 
 to_char（：new.StartDate，'YYYY-MM-DD HH24：MI：SS'）|| 
'CloseDate ='|| to_char .CloseDate，'YYYY-MM-DD HH24：MI：SS'））; 
 END IF; 
 END; 
  
 
I am trying to check add the following constraint but Oracle returns the error shown below.
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:
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.

解决方案
A check constraint, unfortunately, cannot reference a function like SYSDATE.  You would need to create a trigger that checked these values when DML occurs, i.e.
CREATE OR REPLACE TRIGGER trg_check_dates
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
BEGIN
  IF( :new.CloseDate <= SYSDATE )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 
          'Invalid CloseDate: CloseDate must be greater than the current date - value = ' || 
          to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.CloseDate > add_months(SYSDATE,12) )
  THEN
    RAISE_APPLICATION_ERROR( -20002, 
         'Invalid CloseDate: CloseDate must be within the next year - value = ' || 
         to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.StartDate <= add_months(:new.CloseDate,24) )
  THEN
    RAISE_APPLICATION_ERROR( -20002, 
          'Invalid StartDate: StartDate must be within 24 months of the CloseDate - StartDate = ' || 
          to_char( :new.StartDate, 'YYYY-MM-DD HH24:MI:SS' ) ||
          ' CloseDate = ' || to_char( :new.CloseDate , 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
END;


                        
这篇关于在检查约束中使用日期，Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！