PostgreSQL Unique index compound key

Question

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?

Answer 1

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:

• Create unique constraint with null columns

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);