Re: Problem with Check Constraint using NVL

On 5 Feb 2004 12:00:28 -0800, dany.morin_at_mobilair.qc.ca (Dany morin) wrote:

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

[snip various examples where it worked as expected]
>
>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')));

 But I demonstrated that works as expected for various examples in the previous post... (I reformatted it slightly but it's the same code).

 Post examples and explanations of the situations where you think it's not working, since if it's a bug, it's version specific and not showing up here, or I've missed a case.

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