Re: Check Constraint Not Working
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Mon, 17 Aug 2009 10:21:49 -0700 (PDT)
Message-ID: <47f9f7f4-692f-4999-acb3-94c9bcd79e9f_at_24g2000yqm.googlegroups.com>
On Aug 17, 8:41 pm, jimmyb <jimmybr..._at_gmail.com> 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?
/
Date: Mon, 17 Aug 2009 10:21:49 -0700 (PDT)
Message-ID: <47f9f7f4-692f-4999-acb3-94c9bcd79e9f_at_24g2000yqm.googlegroups.com>
On Aug 17, 8:41 pm, jimmyb <jimmybr..._at_gmail.com> 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
Received on Mon Aug 17 2009 - 12:21:49 CDT