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?

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

Original text of this message