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