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

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?



--
George Pavlov
http://mynewplace.com
415.348.2010 desk
415.235.3180 mobile

On 9/6/06, George Pavlov <gpavlov@mynewplace.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. :)

On Wed, 6 Sep 2006 09:29:23 +0200
"Dawid Kuroczko" <qnex42@gmail.com> wrote:

> On 9/6/06, George Pavlov <gpavlov@mynewplace.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

On Wed, Sep 06, 2006 at 09:59:03AM +0200, Ivan Sergio Borgonovo wrote:
> You can write a xor function. At least in 7.4, that I'm currently using, there is no xor operator.

Ah, but there's is, but it's in disguise:

test=# select true <> true, true <> false, false <> true, false <> false;
 ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
 f        | t        | t        | f
(1 row)

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Wed, Sep 06, 2006 at 12:04:18AM -0700, George Pavlov 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?

check (a is null != b is null);