3

I need to create a restriction on a table based on four columns, and two of them are mutually exclusive, I mean, if one is NULL the other is not.

The ideal script would be this:

ALTER TABLE ONLY t_users_prizes
ADD CONSTRAINT t_user_prize_test_circuit_key
UNIQUE (id_event||':'||id_circuit, id_prize, id_user);

But Postgres doesn't allow this kind of concatenation.

How can I implement this restriction?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Carlos Delgado
  • 552
  • 7
  • 23

1 Answers1

13

NULL values do not violate a UNIQUE constraint - two NULL are not considered equal! So a simple UNIQUE constraint doesn't work.

You can get it done with two partial UNIQUE indexes and a CHECK constraint:

CREATE TEMP TABLE foo (
  a int
, b int
, c int NOT NULL
, d int NOT NULL
, CHECK ((a IS NOT NULL AND b IS NULL) OR (b IS NOT NULL AND a IS NULL))
);

CREATE UNIQUE INDEX foo_acd_idx ON foo(a,c,d)
WHERE b is NULL;

CREATE UNIQUE INDEX foo_bcd_idx ON foo(b,c,d)
WHERE a is NULL;

INSERT INTO foo VALUES (NULL,2,3,4);
INSERT INTO foo VALUES (NULL,2,3,4);  -- error!

I declared c and d as NOT NULL, to prevent further complications.

See:

To also disallow (1, NULL, 3, 4) and (NULL, 1, 3, 4), you could use one index with COALESCE instead:

CREATE UNIQUE INDEX foo_xcd_idx ON foo(COALESCE(a,b),c,d);
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • vey good answer! that solves the problem and encourages me to do new things in postgres. Thanks! (Next task: a partitioned table!) – Carlos Delgado Jan 22 '13 at 17:21