Using date in a check constraint, Oracle

问题:

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.

回答1:

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;

回答2:

You cannot use SYSDATE in check constraint. According to documentation

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)
• Calls to user-defined functions
• Dereferencing of REF columns (for example, using the DEREF function)
• Nested table columns or attributes
• The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
• Date constants that are not fully specified

For 10g Release 2 (10.2), see constraint, and for 11g Release 2 (11.2) see constraint.

Remember that an integrity constraint is a statement about table data that is always true.

Anyway: I don't know exactly what you are trying to achieve but I think you can use triggers for this purpose.

回答3:

Write sysdate into a column and use it for validation. This column might be your audit column (For eg: creation date)

CREATE TABLE "AB_EMPLOYEE22"
(
   "NAME"     VARCHAR2 ( 20 BYTE ),
   "AGE"      NUMBER,
   "SALARY"   NUMBER,
   "DOB"      DATE,
   "DOJ"      DATE DEFAULT SYSDATE
);

Table Created    

ALTER TABLE "AB_EMPLOYEE22" ADD CONSTRAINT
AGE_CHECK CHECK((ROUND((DOJ-DOB)/365)) = AGE) ENABLE;

Table Altered

回答4:

Each and every time the record is updated SYSDATE will have a different value. Therefore the constraint will validate differently each time. Oracle does not allow sysdate in a constraint for that reason.

You may be able to solve your problem with a trigger that checks if CloseDate has actually changed and raise an exception when the new value is not within range.

And: What is (StartDate > (CloseDate + (SYSDATE + 730))))? You cannot add dates.

And: StartDate needs to be after CloseDate? Is that not weird?

回答5:

You can achieve this when you do a little cheat like this:

CREATE OR REPLACE FUNCTION SYSDATE_DETERMINISTIC RETURN DATE DETERMINISTIC IS
BEGIN
    RETURN SYSDATE;
END SYSDATE_DETERMINISTIC;
/

CREATE TABLE Table1 (
   s_date DATE, 
   C_DATE DATE GENERATED ALWAYS AS ( SYSDATE_DETERMINISTIC() ) 
);

ALTER TABLE Table1 ADD CONSTRAINT s_check CHECK ( s_date < C_DATE );

Of course, function SYSDATE_DETERMINISTIC is not deterministic but Oracle allows to declare this anyway.

Perhaps in future releases Oracle becomes more intelligent and will not allow such tricks anymore.

回答6:

I don`t recommend sing triggers as constraint and to raise exceptions, Instead you can use a column to store SYSDATE as register date(if you already have it then you can use it) and then your constraint compares this column instead of SYSDATE

 ALTER TABLE Table1
 ADD (REGISTER_DATE DATE);

 CREATE OR REPLACE TRIGGER trg_check_dates
   BEFORE INSERT OR UPDATE ON table1
   FOR EACH ROW
 BEGIN
   :new.REGISTER_DATE := SYSDATE;
 END;

 ALTER TABLE Table1
 ADD (CONSTRAINT GT_Table1_CloseDate
 CHECK (CloseDate > REGISTER_DATE),
 CONSTRAINT LT_Table1_CloseDate
 CHECK (CloseDate <= REGISTER_DATE + 365)),
 CONSTRAINT GT_Table1_StartDate
 CHECK (StartDate > (CloseDate + (REGISTER_DATE + 730))));