On Wed, 6 Sep 2006 09:29:23 +0200 "Dawid Kuroczko" <qnex42@xxxxxxxxx> wrote: > On 9/6/06, George Pavlov <gpavlov@xxxxxxxxxxxxxx> 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
