Re: Constraint that compares and limits field values

On Wed, Jan 25, 2006 at 09:55:58AM -0800, MargaretGillon@chromalloy.com wrote:
> You are correct, in each group of three columns, one needs to have an
> integer and the other two need to be NULL. So I need to modify the
> constraint to be....
>
> ALTER TABLE event
> ADD CONSTRAINT two_nulls_1 CHECK
> ((evenid1 IS NULL) AND (evevid1 IS NULL) and (evreid1 IS NOT NULL)) OR
> ((evenid1 IS NULL) AND (evevid1 IS NOT NULL) and (evreid1 IS NULL)) OR
> ((evenid1 IS NOT NULL) AND (evevid1 IS NULL) and (evreid1 IS  NULL))
>
> Correct?

That looks right, aside from a syntax error from not having parentheses
around the entire expression.

CREATE TABLE event (
    id       serial PRIMARY KEY,
    evenid1  integer,
    evevid1  integer,
    evreid1  integer
);

ALTER TABLE event
ADD CONSTRAINT two_nulls_1 CHECK (
((evenid1 IS NULL) AND (evevid1 IS NULL) and (evreid1 IS NOT NULL)) OR
((evenid1 IS NULL) AND (evevid1 IS NOT NULL) and (evreid1 IS NULL)) OR
((evenid1 IS NOT NULL) AND (evevid1 IS NULL) and (evreid1 IS  NULL)));

INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, NULL, NULL);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, NULL, 1);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, 1, NULL);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, 1, 1);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, NULL, NULL);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, NULL, 1);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, 1, NULL);
INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, 1, 1);

SELECT * FROM event;
 id | evenid1 | evevid1 | evreid1
----+---------+---------+---------
  2 |         |         |       1
  3 |         |       1 |
  5 |       1 |         |
(3 rows)

In 8.1, and in earlier versions if you create a cast from boolean
to integer, you could do this:

ALTER TABLE event
ADD CONSTRAINT two_nulls_1 CHECK (
(evenid1 IS NOT NULL)::int +
(evevid1 IS NOT NULL)::int +
(evreid1 IS NOT NULL)::int = 1);

This works because the cast converts true to 1 and false to 0;
you're adding up the number of true expressions and requiring that
the sum equal 1 (i.e., that exactly one expression be true).

--
Michael Fuhr