Re: Problem with Check Constraint using NVL

I know that the second declaration of this constraint works well. However, I want to understant why the first one doesn't work.

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')));

Andy Hassall <andy_at_andyh.co.uk> wrote in message news:<v73b105c2sgq8o3a6ua7hfm37013jrgg1o_at_4ax.com>...
> 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.
Received on Thu Feb 05 2004 - 14:00:28 CST