Check Constraint Not Working

From: jimmyb <jimmybrock_at_gmail.com>
Date: Mon, 17 Aug 2009 09:41:19 -0700 (PDT)
Message-ID: <48b8e5c6-15a9-4e3d-b2e1-c1fd7cd79e6f_at_g31g2000yqc.googlegroups.com>

I have the following table:

SQL> create table nums

  2  ( col1  number   ,
  3    col2  number   ,
  4    col3  number   ,
  5    col4  number   ,
  6    col5  number   ,

  7 col6 number
  8 )
  9 tablespace users
 10 /

Table created.

Here are the business rules:

/*

     #1 rule
     if        col2 is not null
     then   col1 is not null

     #2 rule
     if        col3 is not null
     then   col2 is not null

     #3 rule
     if        col4 is not null
     then   col3 is not null

     #4 rule
     if        col5 is not null
     then   col4 is not null

     #5 rule
     if        col6 is not null
     then   col5 is not null

*/

Here is the constraint to enforce the business rules:

SQL> alter table nums

                      add constraint nums_not_null_chk
                         CHECK( CASE
			          WHEN   col2 is not null
				and  col1 is not null
			          THEN 1
			          WHEN   col3 is not null
				and  col2 is not null
			          THEN 1
			          WHEN   col4 is not null
				and  col3 is not null
			          THEN 1
			          WHEN   col5 is not null
				and  col4 is not null
			          THEN 1
			          WHEN   col6 is not null
				and  col5 is not null
			          THEN 1
				     ELSE 0
			        END  = 1
			     ) ;

Table altered.

/* test rule #1 */

SQL> insert into nums (col1, col2) values(null,1) ; insert into nums (col1, col2) values(null,1) *
ERROR at line 1:
ORA-02290: check constraint (DEMO.NUMS_NOT_NULL_CHK) violated

SQL> insert into nums (col1, col2) values(1,1) ;

1 row created.

/* rule #1 works correctly */

/* test rule #2 */

SQL> insert into nums (col1, col2, col3) values(1,null,2) ; insert into nums (col1, col2, col3) values(1,null,2) *
ERROR at line 1:
ORA-02290: check constraint (DEMO.NUMS_NOT_NULL_CHK) violated

SQL> insert into nums (col1, col2, col3) values(1,2,2) ;

1 row created.

/* rule #2 works correctly */

/* test rule #3 */

SQL> insert into nums (col1,col2,col3,col4) values(1,2,null,3) ;

1 row created.

/* rule #3 FAILED! */

Why is rule #3 failing? Rules 3,4 and 5 all fail, but rules #1 and #2 are enforced.

Any ideas? Received on Mon Aug 17 2009 - 11:41:19 CDT