Re: Problem with Check Constraint using NVL

Can you tell us the data type of the two columns, a couple of examples where the constraint fails, and the version number of the database.

I'm getting the expected behaviour.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG -  v$ and x$
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Dany morin" <dany.morin_at_mobilair.qc.ca> wrote in message
news:fe2795e4.0401261019.1cd6ff7d_at_posting.google.com...


> 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