Thread: help writing a constraint

I have a table with a pair of columns that shouldn't both have values:

CREATE TABLE Foo (
    id SERIAL not null,
    hasBar integer default null REFERERENCES Bar,
    hasBaz integer default null REFERERENCES Baz,
    primary key (id)
);

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)

--Greg

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)