Re: Check Constraint Not Working

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 17 Aug 2009 23:22:40 +0200
Message-ID: <4A89CA20.5090500_at_gmail.com>

jimmyb wrote:
> On Aug 17, 12:14 pm, Ed Prochak <edproc..._at_gmail.com> wrote:

>> On Aug 17, 12: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.
>> []
>>
>>> Why is rule #3 failing? Rules 3,4 and 5 all fail, but rules #1 and #2
>>> are enforced.
>>> Any ideas?
>> Does it have to be only one constraint? The business rules look to me
>> like 5 separate constraints. Have you tried that?
>>    Ed- Hide quoted text -
>>
>> - Show quoted text -

>
> I got to work correctly:
>
> jbrock_at_orcl-local> 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.
>
> /* business rules
>
> #1 rule
> if col is not null
> then valid record
>
> #2 rule
> if col2 is not null
> then col1 is not null
>
> #3 rule
> if col3 is not null
> then col2 is not null
>
> #4 rule
> if col4 is not null
> then col3 is not null
>
> #5 rule
> if col5 is not null
> then col4 is not null
>
> #6 rule
> if col6 is not null
> then col5 is not null
>
> */
>
> /* create constraint to enforce business rules */
> jbrock_at_orcl-local> alter table nums add constraint nums_not_null_chk
> 2 CHECK( CASE
> 3 WHEN col6 is not null
> 4 and col5 is not null
> 5 and col4 is not null
> 6 and col3 is not null
> 7 and col2 is not null
> 8 and col1 is not null
> 9 THEN 1
> 10 WHEN col6 is null
> 11 and col5 is not null
> 12 and col4 is not null
> 13 and col3 is not null
> 14 and col2 is not null
> 15 and col1 is not null
> 16 THEN 1
> 17 WHEN col6 is null
> 18 and col5 is null
> 19 and col4 is not null
> 20 and col3 is not null
> 21 and col2 is not null
> 22 and col1 is not null
> 23 THEN 1
> 24 WHEN col6 is null
> 25 and col5 is null
> 26 and col4 is null
> 27 and col3 is not null
> 28 and col2 is not null
> 29 and col1 is not null
> 30 THEN 1
> 31 WHEN col6 is null
> 32 and col5 is null
> 33 and col4 is null
> 34 and col3 is null
> 35 and col2 is not null
> 36 and col1 is not null
> 37 THEN 1
> 38 WHEN col6 is null
> 39 and col5 is null
> 40 and col4 is null
> 41 and col3 is null
> 42 and col2 is null
> 43 and col1 is not null
> 44 THEN 1
> 45 ELSE 0
> 46 END = 1
> 47 ) ;
>
> Table altered.
>
> jbrock_at_orcl-local> /* test valid inserts */
> jbrock_at_orcl-local> insert into nums values(1,2,3,4,5,6) ;
>
> 1 row created.
>
> jbrock_at_orcl-local> insert into nums values(1,2,3,4,5,null) ;
>
> 1 row created.
>
> jbrock_at_orcl-local> insert into nums values(1,2,3,4,null,null);
>
> 1 row created.
>
> jbrock_at_orcl-local> insert into nums values(1,2,3,null,null,null);
>
> 1 row created.
>
> jbrock_at_orcl-local> insert into nums values(1,2,null,null,null,null);
>
> 1 row created.
>
> jbrock_at_orcl-local> insert into nums values
> (1,null,null,null,null,null);
>
> 1 row created.
>
> jbrock_at_orcl-local> /* test invalid inserts */
> jbrock_at_orcl-local> insert into nums values(null,
> 1,null,null,null,null);
> insert into nums values(null,1,null,null,null,null)
> *
> ERROR at line 1:
> ORA-02290: check constraint (JBROCK.NUMS_NOT_NULL_CHK) violated
>
>
> jbrock_at_orcl-local> insert into nums values(1,null,3,null,null,null);
> insert into nums values(1,null,3,null,null,null)
> *
> ERROR at line 1:
> ORA-02290: check constraint (JBROCK.NUMS_NOT_NULL_CHK) violated
>
>
> jbrock_at_orcl-local> insert into nums values(1,2,null,4,null,null);
> insert into nums values(1,2,null,4,null,null)
> *
> ERROR at line 1:
> ORA-02290: check constraint (JBROCK.NUMS_NOT_NULL_CHK) violated
>
>
> jbrock_at_orcl-local> insert into nums values(1,2,3,null,5,6);
> insert into nums values(1,2,3,null,5,6)
> *
> ERROR at line 1:
> ORA-02290: check constraint (JBROCK.NUMS_NOT_NULL_CHK) violated
>
>
> jbrock_at_orcl-local> insert into nums values(1,2,3,4,null,6);
> insert into nums values(1,2,3,4,null,6)
> *
> ERROR at line 1:
> ORA-02290: check constraint (JBROCK.NUMS_NOT_NULL_CHK) violated

Slightly shorter notation, ( but probably less readable as well):

alter table nums add constraint ck_con check( bin_to_num(

   nvl2(col6,1,0),
   nvl2(col5,1,0),
   nvl2(col4,1,0),
   nvl2(col3,1,0),
   nvl2(col2,1,0),
   nvl2(col1,1,0)

) in
(0,1,3,7,15,31,63)
)

If all NULL values in row at same time are not allowed, then 0 should be removed from the in list

Best regards

Maxim Received on Mon Aug 17 2009 - 16:22:40 CDT