Re: Multi column check constraint

From: Jeff <jglenn_at_nospam.ep-services.com>
Date: 2000/05/23
Message-ID: <zFBW4.21$xg1.2567_at_news.pacbell.net>#1/1

"Philip Lijnzaad" <lijnzaad_at_ebi.ac.uk> wrote in message news:u7g0rb0zdu.fsf_at_o2-3.ebi.ac.uk...
>
> >> alter table foobar add constraint exactly_one_null check (
> >> (item_link_no is not null and voucher_id is not null) or
> >> (item_link_no is null and voucher_id is not null) or
> >> (item_link_no is not null and voucher_id is null));
>
> Heinz> A simpler condition should be
> Heinz> item_link_no is not null or voucher_id is not null
>
> ah, yes, of course, I started from the (wrong) assumption that exactly one
> should be non-null, then added the 'both not null' as afterthought.
 Cheers,
>
>

Philip

Actually, Philip, your assumption was correct. Exactly one column must be not null. Both columns
cannot be not null, and both columns cannot be null. Sorry, I should have been more clear in
my original post.

In other words...

insert into foobar2 (item_link_no, voucher_id) values ('x', NULL);
insert into foobar2 (item_link_no, voucher_id) values ( NULL, 0);
insert into foobar2 (item_link_no, voucher_id) values ( NULL, NULL); <-- NOT

ALLOWED!
insert into foobar2 (item_link_no, voucher_id) values ('y', 0); <-- NOT ALLOWED!

This works as desired (at least on Informix): check ((item_link_no is null or voucher_id is null) AND (item_link_no is not null or voucher_id is
not null)) constraint exactly_one_null;

Thanks for your help,
Jeff Received on Tue May 23 2000 - 00:00:00 CEST