Re: constraint -- one or the other column not null

On Wed, 6 Sep 2006 09:29:23 +0200
"Dawid Kuroczko" <qnex42(at)gmail(dot)com> wrote:

> On 9/6/06, George Pavlov <gpavlov(at)mynewplace(dot)com> wrote:
> > I have two columns, both individually nullable, but a row needs
> > to have a value in one or the other. What is the best way to
> > implement the constraints? I currently have:
> >
> > create table f (
> > a int,
> > b int,
> > check (a + b is null),
> > check (coalesce(a,b) is not null)
> > );
> >
> > Is there a better way to do it?
>
> Personally I woud simply put there
> CHECK(a IS NOT NULL OR b IS NOT NULL)
> which is probably the simplest form of your constraint. :)

in one or the other should stand for xor not xor.
And yeah George's solution seems the most concise for one shot. I don't know if it is faster than:
check ((a is null and b is not null) or ( a is not null and b is null))

You can write a xor function. At least in 7.4, that I'm currently using, there is no xor operator.

create function xor(boolean,boolean)
returns boolean as '
begin
select ($1 and not $2) or (not $1 and $2);
end;
' language 'sql';

check (xor(is null a, is null b))

sort of

Anyway if one of the 2 has to be null, why don't you use a boolean column + an int?

create table f (
ab int,
aorb boolean,
);

--
Ivan Sergio Borgonovo
http://www.webthatworks.it