Re: help writing a constraint

Gregory Seidman dijo:

>
> I need to constrain this so that for any given row, it either hasBar or
> hasBaz, or neither, but not both. I'm not entirely clear on how to write
> constraints, though. Is the following correct (within the CREATE)?
>
> CHECK (hasBar IS NULL OR hasBaz IS NULL)

CREATE TABLE Foo (
id SERIAL not null,
hasBar integer default null REFERENCES Bar,
hasBaz integer default null REFERENCES Baz,
primary key (id),
CHECK (hasBar IS NULL OR hasBaz IS NULL)
);

Why don't you try it out before asking? You were perfectly right.

You can also give a name to constraints to get more meaningful messages
(useful if you have lots of constraints):

CREATE TABLE Foo (
id SERIAL not null,
hasBar integer default null REFERENCES Bar,
hasBaz integer default null REFERENCES Baz,
primary key (id),
constraint onlyOne CHECK (hasBar IS NULL OR hasBaz IS NULL)
);

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Siempre hay que alimentar a los dioses, aunque la tierra este seca" (Orual)