Using date in check constraint, Oracle

Question

I am trying to verify the addition of the following restriction, 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.

Answer 1

Unfortunately, the control constraint cannot reference a function of type SYSDATE. You will need to create a trigger that checks these values ​​when DML is encountered, 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;

Answer 2

You cannot use SYSDATE in a check constraint. According to the documentation

The constraint verification conditions cannot contain the following constructs:

• Subqueries and scalar subquery expressions
• (CURRENT_DATE,
  CURRENT_TIMESTAMP, DBTIMEZONE,
  LOCALTIMESTAMP, SESSIONTIMEZONE,
  SYSDATE, SYSTIMESTAMP, UID, USER USERENV)
• REF (, DEREF)
• CURRVAL, NEXTVAL, LEVEL ROWNUM
• ,

10g Release 2 (10.2), . , 11g Release 2 (11.2) . .

, - , .

: , , , .

Answer 3

, , SYSDATE . - . Oracle sysdate .

, , , CloseDate , , .

: (StartDate > (CloseDate + (SYSDATE + 730))))? .

: StartDate CloseDate? ?

Answer 4

sysdate . (, )

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

Answer 5

, :

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 );

, SYSDATE_DETERMINISTIC , Oracle .

Perhaps in future releases Oracle will become more intelligent and will no longer allow such tricks.

Answer 6

I do not recommend using triggers as a constraint and throwing exceptions. Instead, you can use the column to store SYSDATE as the registration date (if you already have one, then you can use it), and then your restriction compares that 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))));