Re: Problem with Check Constraint using NVL

On Mon, 26 Jan 2004 13:11:16 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

>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).
>
>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.

 I don't follow the reasoning - could you explain what's wrong with a constraint referencing two or more columns in the same row please? Why's a trigger preferred? You'd have to cover insert and update of both columns, whereas the check constraint is a one-liner enforced at all times by Oracle.

>Plus you "logic" isn't ... what is the meaning of
>
>AND type1 IS NOT NULL AND type1 IN ('1','2','3','4','N','U')));

 If it's going to be in the list, it's got to be NOT NULL first.

 "NULL IN ('x', 'y', 'z')" is neither true nor false, it's unknown, and if a check constraint predicate evaluates to unknown the row is accepted - the OP wanted it rejected according to his spec.

SQL> create table x (c varchar2(1), constraint x_c1 check (c in ('X', 'Y', 'Z')));

Table created

SQL> insert into x values (null);

1 row inserted

SQL> alter table x drop constraint x_c1;

Table altered

SQL> delete from x;

1 row deleted

SQL> alter table x add constraint x_c1 check (c is not null and c in ('X', 'Y', 'Z'));

Table altered

SQL> insert into x values (null);

insert into x values (null)

ORA-02290: check constraint (TEST.X_C1) violated

SQL> insert into x values ('X');

1 row inserted

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