Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Check constraint issue

PSpenceDec 10 2012 — edited Dec 11 2012
I 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>
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 8 2013
Added on Dec 10 2012
4 comments
2,844 views