Re: database constraints

On Wed, Oct 06, 2004 at 08:23:11AM -0700, Ben wrote:
> If I have have the table:
>
> create table foo
> (
> a int references bar(id),
> b int references baz(id)
> )
>
> ... how do I make sure one and only one of the columns a and b are
> non-null?

You could write it like this:

CREATE TABLE foo
(
a INT REFERENCES bar(id),
b INT REFERENCES baz(id),
CHECK((a IS NULL AND b IS NOT NULL) OR (a IS NOT NULL AND b IS NULL))
);

> Is it even reasonable?

What's "reasonable?" ;)

BTW, "id" is a terrible name for a column. Better call it foo_id.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!