Problem with Check Constraint using NVL

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 Received on Mon Jan 26 2004 - 12:19:26 CST