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) ...
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
this is caused by the line