Check constraint with NULL check, evaluates to TRUE for all values
895463Dec 13 2011 — edited Feb 10 2012Hi,
We are using Oracle 10.2.0.4 on RHEL4 64 bit OS.
Recently we encountered something surprising related to check constraints. Our check constraint accepts all values!
For example,
CREATE TABLE EMPLOYEE
(
ENO NUMBER,
NAME VARCHAR2(500 CHAR),
GROUP_TYPE CHAR(1 CHAR)
)
ALTER TABLE EMPLOYEE ADD (
CONSTRAINT EMPLOYEE_CK01
CHECK (group_type IN ('A','B','C','D',NULL)));
Above check constraint accepts any character. This is surprising us.
For example,
If we execute below simple SELECT, it doesn't returns any value i.e. the WHERE condition becomes FALSE and no records are returned.
select 1 from dual where 1 in (NULL)
Whereas, in case of below SQL, the WHERE condition becomes TRUE and 1 record is returned.
Hence we think that, similarly the IN (NULL) condition for check constraint should become FALSE and gives CHECK Constraint error, "ORA-02290: check constraint (COB.EMPLOYEE_CK01) violated".
But the check constraint IN clause becomes TRUE and allows insertion of record in the table.
Is this expected behaviour? or is it a known BUG?
Regards
Shailendra