Re: Check Constraint Not Working

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Mon, 17 Aug 2009 21:01:18 -0700 (PDT)
Message-ID: <a1381277-cd62-4a7f-9005-5104afbc50ec_at_32g2000yqj.googlegroups.com>

On Aug 18, 3:07 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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

Indeed, I didn't test it myself and my initial logic was flawed. :) Your bitmask solution is more elegant anyway.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon Aug 17 2009 - 23:01:18 CDT