Check constraint issue
PSpenceDec 10 2012 — edited Dec 11 2012I have a table with a check constraint that does not appear to be working as I would expect. The issue appears to be related to when a 'NULL' is included as an acceptable value in a check constraint. The following shows what I am talking about. The first example correctly disallows any value except 'Y' or 'N'. However, when the check constraint is altered to include an allowable value of NULL, you can insert a '1' where only 'Y', 'N', or null should be allowed. Any thoughts?
create table x (var1 varchar2(10));
Table created.
SQL> alter table x add(constraint test_const check (var1 in ('Y','N')));
Table altered.
SQL> insert into x (var1) values ('1');
insert into x (var1) values ('1')
*
ERROR at line 1:
ORA-02290: check constraint (TEST_CONST) violated
SQL> ALTER TABLE x drop constraint test_const;
Table altered.
SQL> alter table x add(constraint test_const check (var1 in ('Y','N',NULL)));
Table altered.
SQL> insert into x (var1) values ('1');
1 row created.
SQL>