Re: Problem with Check Constraint using NVL

On 26 Jan 2004 10:19:26 -0800, dany.morin_at_mobilair.qc.ca (Dany morin) wrote:

>I use a constraint to identify the following business rules:
>
>1- if code1 is null, then type1 must be null
>2- if code1 is not null, then type1 must not be null and must be in
>(1,2,3,4,N,U).
>
>If I use the following constraint, the constraint is added to the
>table but the behavior is not correct when I try to edit the rows of
>the table, and I don't understand why:
>
>ALTER TABLE x ADD CONSTRAINT ck_x_c3 CHECK ((code1 IS NULL AND type1
>IS NULL) OR (code1 IS NOT NULL AND NVL(type1 ,'-1') IN
>('1','2','3','4','N','U')));
>
>
>However, if I use the following constraint, all is fine. It's strange
>because it seems like the same for me...
>
>ALTER TABLE x ADD CONSTRAINT ck_x_c3 CHECK ((code1 IS NULL AND type1
>IS NULL) OR (code1 IS NOT NULL AND type1 IS NOT NULL AND type1 IN
>('1','2','3','4','N','U')));
>
>
>Can you help me to understand please. Is it a bug with NVL in a CHECK
>constraint ?.

 What problems are you having exactly? Looks OK to me, from a few combinations anyway:

SQL> create table x (code1 varchar2(16), type1 varchar2(16));

Table created.

SQL> alter table x
  2 add constraint ck_x_c3
  3 check ( (code1 IS NULL AND type1 IS NULL)

  4         OR (code1 IS NOT NULL
  5             AND NVL(type1 ,'-1') IN ('1','2','3','4','N','U')));

Table altered.

SQL> insert into x values (null, null);

1 row created.

SQL> update x set code1 = 'X';
update x set code1 = 'X'
*
ERROR at line 1:
ORA-02290: check constraint (TEST.CK_X_C3) violated

SQL> update x set code1 = 'X', type1 = 'Y'; update x set code1 = 'X', type1 = 'Y'
*
ERROR at line 1:
ORA-02290: check constraint (TEST.CK_X_C3) violated

SQL> update x set code1 = 'X', type1 = 'N';

1 row updated.

SQL> update x set type1 = 'X';
update x set type1 = 'X'
*
ERROR at line 1:
ORA-02290: check constraint (TEST.CK_X_C3) violated

SQL> update x set code1 = null;
update x set code1 = null
*
ERROR at line 1:
ORA-02290: check constraint (TEST.CK_X_C3) violated

SQL> update x set type1 = null;
update x set type1 = null
*
ERROR at line 1:
ORA-02290: check constraint (TEST.CK_X_C3) violated

SQL> update x set code1 = null, type1 = null;

1 row updated.

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>