Re: Check Constraint Not Working
Date: Mon, 17 Aug 2009 11:07:35 -0700 (PDT)
Message-ID: <9ea02c1e-3e8c-4374-91fa-22c94b67d2a3_at_n11g2000yqb.googlegroups.com>
On Aug 17, 12:21 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> 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- Hide quoted text -
>
> - Show quoted text -
Let's see what works:
SQL> -- SQL> -- Create test table SQL> -- 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.
SQL> SQL> -- SQL> -- Attempt to create a check constraint SQL> -- SQL> alter table nums 2 add constraint nums_not_null_chk 3 CHECK( CASE 4 WHEN col6 is not null 5 and col5 is not null 6 THEN 1 7 WHEN col5 is not null 8 and col4 is not null 9 THEN 1 10 WHEN col4 is not null 11 and col3 is not null 12 THEN 1 13 WHEN col3 is not null 14 and col2 is not null 15 THEN 1 16 WHEN col2 is not null 17 and col1 is not null 18 THEN 1 19 ELSE 0 20 END = 1
21 ) ;
Table altered.
SQL> SQL> -- SQL> -- Try inserts, see if the violating data fails SQL> -- 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 (BING.NUMS_NOT_NULL_CHK) violated
SQL> insert into nums (col1, col2) values(1,2) ;
1 row created.
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 (BING.NUMS_NOT_NULL_CHK) violated
SQL> insert into nums (col1, col2, col3) values(1,2,2) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4) values(1,2,null,3) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4) values(1,2,3,4) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5) values(1,2,3,null, 5) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5) values(1,2,3,4,5) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5,col6) values (1,2,3,4,null,6) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5,col6) values (1,2,3,4,5,6) ;
1 row created.
SQL>
SQL> --
SQL> -- Select inserted data
SQL> --
SQL> select * From nums;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
1 2
1 2 2
1 2 3
1 2 3 4
1 2 3 5
1 2 3 4 5
1 2 3 4 6
1 2 3 4 5 6
8 rows selected.
SQL> SQL> -- SQL> -- Truncate table, drop constraint SQL> -- SQL> truncate table nums;
Table truncated.
SQL> alter table nums drop constraint nums_not_null_chk;
Table altered.
SQL> SQL> -- SQL> -- Check constraint didn't cut the mustard SQL> -- SQL> -- Let's try a trigger SQL> -- SQL> create trigger nums_not_null_ck_trg2 before insert or update on nums
3 for each row
4 declare
5 errtxt varchar2(100); 6 coll_null exception; 7 pragma exception_init(coll_null, -20999); 8 begin 9 if :new.col1 is null and :new.col2 is not null then 10 errtxt:='COL1 cannot be null when COL2 is not null'; 11 raise coll_null; 12 end if; 13 if :new.col2 is null and :new.col3 is not null then 14 errtxt:='COL2 cannot be null when COL3 is not null'; 15 raise coll_null; 16 end if; 17 if :new.col3 is null and :new.col4 is not null then 18 errtxt:='COL3 cannot be null when COL4 is not null'; 19 raise coll_null; 20 end if; 21 if :new.col4 is null and :new.col5 is not null then 22 errtxt:='COL4 cannot be null when COL5 is not null'; 23 raise coll_null; 24 end if; 25 if :new.col5 is null and :new.col6 is not null then 26 errtxt:='COL5 cannot be null when COL6 is not null'; 27 raise coll_null; 28 end if; 29 exception 30 when coll_null then 31 raise_application_error(-20999, errtxt, true);32 end;
33 /
Trigger created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Try the inserts again
SQL> --
SQL> insert into nums (col1, col2) values(null,1) ;
insert into nums (col1, col2) values(null,1)
*
ERROR at line 1:
ORA-20999: COL1 cannot be null when COL2 is not null ORA-06512: at "BING.NUMS_NOT_NULL_CK_TRG", line 28 ORA-20999: ORA-04088: error during execution of trigger'BING.NUMS_NOT_NULL_CK_TRG' SQL> insert into nums (col1, col2) values(1,2) ;
1 row created.
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-20999: COL2 cannot be null when COL3 is not null ORA-06512: at "BING.NUMS_NOT_NULL_CK_TRG", line 28 ORA-20999: ORA-04088: error during execution of trigger'BING.NUMS_NOT_NULL_CK_TRG' SQL> insert into nums (col1, col2, col3) values(1,2,2) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4) values(1,2,null,3) ; insert into nums (col1,col2,col3,col4) values(1,2,null,3)
*
ERROR at line 1:
ORA-20999: COL3 cannot be null when COL4 is not null ORA-06512: at "BING.NUMS_NOT_NULL_CK_TRG", line 28 ORA-20999: ORA-04088: error during execution of trigger'BING.NUMS_NOT_NULL_CK_TRG' SQL> insert into nums (col1,col2,col3,col4) values(1,2,3,4) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5) values(1,2,3,null,
5) ;
insert into nums (col1,col2,col3,col4,col5) values(1,2,3,null,5)
*
ERROR at line 1:
ORA-20999: COL4 cannot be null when COL5 is not null ORA-06512: at "BING.NUMS_NOT_NULL_CK_TRG", line 28 ORA-20999: ORA-04088: error during execution of trigger'BING.NUMS_NOT_NULL_CK_TRG' SQL> insert into nums (col1,col2,col3,col4,col5) values(1,2,3,4,5) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5,col6) values
(1,2,3,4,null,6) ;
insert into nums (col1,col2,col3,col4,col5,col6) values(1,2,3,4,null,
6)
*
ERROR at line 1:
ORA-20999: COL5 cannot be null when COL6 is not null ORA-06512: at "BING.NUMS_NOT_NULL_CK_TRG", line 28 ORA-20999: ORA-04088: error during execution of trigger'BING.NUMS_NOT_NULL_CK_TRG' SQL> insert into nums (col1,col2,col3,col4,col5,col6) values (1,2,3,4,5,6) ;
1 row created.
SQL>
SQL> --
SQL> -- Select inserted data
SQL> --
SQL> select * From nums;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
1 2
1 2 2
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
SQL>
SQL> drop trigger nums_not_null_ck_trg;
Trigger dropped.
SQL>
SQL> truncate table nums;
Table truncated.
SQL> SQL> -- SQL> -- Try another approach SQL> -- SQL> ALTER TABLE nums ADD CONSTRAINT nums_not_null_chk 2 CHECK (col6+col5+col4+col3+col2+col1 is not null or 3 col5+col4+col3+col2+col1 is not null or 4 col4+col3+col2+col1 is not null or 5 col3+col2+col1 is not null or 6 col2+col1 is not null)
7 /
Table altered.
SQL> SQL> -- SQL> -- Try the inserts again SQL> -- 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 (BING.NUMS_NOT_NULL_CHK) violated
SQL> insert into nums (col1, col2) values(1,2) ;
1 row created.
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 (BING.NUMS_NOT_NULL_CHK) violated
SQL> insert into nums (col1, col2, col3) values(1,2,2) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4) values(1,2,null,3) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4) values(1,2,3,4) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5) values(1,2,3,null, 5) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5) values(1,2,3,4,5) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5,col6) values (1,2,3,4,null,6) ;
1 row created.
SQL> insert into nums (col1,col2,col3,col4,col5,col6) values (1,2,3,4,5,6) ;
1 row created.
SQL>
SQL> --
SQL> -- Select inserted data
SQL> --
SQL> select * From nums;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
1 2
1 2 2
1 2 3
1 2 3 4
1 2 3 5
1 2 3 4 5
1 2 3 4 6
1 2 3 4 5 6
8 rows selected.
SQL> Looks like the trigger, from the example. Of course this isn't exhaustive and someone may find a check constraint configuration which works.
David Fitzjarrell Received on Mon Aug 17 2009 - 13:07:35 CDT