constraint -- one or the other column not null

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/

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