I have a column in a table that can contain three possible values: 'A','B' or null.
I have created a check constraint on the field like this:
ALTER TABLE EMPLOYEE
ADD CONSTRAINT TYPE_TYPE_CHK CHECK
(TYPE_TYPE IN ('A', 'B'))
ENABLE;
When I try to insert a new row into the table with null in the TYPE_TYPE field, I get the following error:
SQL Error: ORA-02290: check constraint (HR.TYPE_TYPE_CHK) violated
02290. 00000 - "check constraint (%s.%s) violated"
*Cause: The values being inserted do not satisfy the named check
*Action: do not insert values that violate the constraint.
However, when I perform an update to change the existing value to a null, I get no error.
Does anyone know why I am unable to insert a null?
I have tried adding "OR TYPE_TYPE IS NULL" to the constraint with no change.
I am currently using Oracle Database 11g 11.2.0.4.0.