Re: Check Constraint Not Working

Vladimir M. Zakharychev wrote:
On Aug 17, 8:41 pm, jimmyb <jimmybr...@xxxxxxxxx> wrote:
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?

Because Rule #1 is satisfied and CASE evaluation stops right there. If
I got your rules correctly, only trailing columns can be null except
when all columns are null. One possible solution is this:

ALTER TABLE nums ADD CONSTRAINT nums_not_null_chk
CHECK (col6+col5+col4+col3+col2+col1 is not null or
col5+col4+col3+col2+col1 is not null or
col4+col3+col2+col1 is not null or
col3+col2+col1 is not null or
col2+col1 is not null)
/

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

I think, your check constraint checks properly for NOT NULL columns, but doesnt't for NULL columns. Seems to me, it should be sligthly extended to work as OP expected:


ALTER TABLE nums ADD CONSTRAINT nums_not_null_chk
CHECK ((col6+col5+col4+col3+col2+col1 is not null) or
(col5+col4+col3+col2+col1 is not null and col6 is null) or
(col4+col3+col2+col1 is not null and coalesce(col6,col5) is null) or
(col3+col2+col1 is not null and coalesce(col6,col5,co4) is null)
or
(col2+col1 is not null and coalesce(col6,col5,col4,col3) is null)
or (col1 is not null and coalesce(col6,col5,col4,col3,col2) is null)
)

Best regards

Maxim
.

Relevant Pages
Loading