Re: Check Constraint Not Working

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 18 Aug 2009 01:07:24 +0200
Message-ID: <4A89E2AC.30900_at_gmail.com>

Vladimir M. Zakharychev wrote:

> 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

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 Received on Mon Aug 17 2009 - 18:07:24 CDT