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@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!