Oracle – ORA-02436: Date or System Variable Wrongly Specified in CHECK Constraint

oracleoracle-sql-developer

This is the table I have created:

 CREATE TABLE Ticket_Type
 (
  t_type_id  NUMBER(1) PRIMARY KEY,
  CONSTRAINT check_t_type_id CHECK(t_type_id  > 0),
  t_type VARCHAR(20) NOT NULL,
  t_type_price NUMBER(4,2) NOT NULL,
  CONSTRAINT check_t_type_price CHECK(t_type_price > 0),
  t_type_start_date DATE NOT NULL,
  t_type_end_date DATE,
  CONSTRAINT check_t_type_end_date CHECK((t_type_end_date = NULL) OR (t_type_end_date >= t_type_start_date)),
  CONSTRAINT unique_t_type_t_type_start_date UNIQUE(t_type,t_type_start_date)
  );

I tried to create this table but I was getting the following error message:

Error report -
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.

I'm unable to figure out regarding where is the mistake I'm making.

I'm using Oracle version 12c SQL Developer.

Best Answer

Try (for the CHECK constraint) ...

t_type_end_date IS NULL

Also, when using your DDL code in apex.oracle.com (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production), we get

ORA-00972: identifier is too long

this is caused by the line

CONSTRAINT unique_t_type_t_type_start_date UNIQUE(t_type,t_type_start_date)