Re: Check Constraint Not Working
Date: Mon, 17 Aug 2009 12:26:24 -0700 (PDT)
Message-ID: <1773cc62-34d5-4d27-b51c-49f17574df02_at_o6g2000yqj.googlegroups.com>
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
Received on Mon Aug 17 2009 - 14:26:24 CDT