Re: Problem with Check Constraint using NVL

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 ?.
>
> Thanks

A check constraint referencing another column in the table? I think you are looking for a trigger. You are using the wrong tool for the job.

Plus you "logic" isn't ... what is the meaning of

AND type1 IS NOT NULL AND type1 IN ('1','2','3','4','N','U')));

Drop the check constraint, write a trigger, clean up your logic.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)