Compound unique constraint with NULL values

Question

With the following table structure

CREATE TABLE favorite_comments(
   id SERIAL PRIMARY KEY NOT NULL,
   opinion_id BIGINT REFERENCES opinions(id),
   review_id BIGINT REFERENCES reviews(id),
   list_id INTEGER REFERENCES lists(id) NOT NULL
);

This are valid records (Review and opinion related to the same list):

id,  opinion_id,  review_id,  list_id
-------------------------------------
1,          333,       NULL,        4
2,         NULL,        444,        4

But i would like to prevent that same review or opinion can appear twice related to the same list:

id,  opinion_id,  review_id,  list_id
-------------------------------------
1,          333,       NULL,        4
2,         NULL,        444,        4
3,         NULL,        444,        4 <- WRONG

I would like to add a constraint that checks a conditional uniqueness, UNIQUE(opinion_id, list_id) OR UNIQUE(review_id, list_id), but since opinion_id,review_id can be NULL im having some trouble with this

Ive tried this but does not work

ALTER TABLE favorite_comments ADD CONSTRAINT comments_can_only_be_saved_once_per_list UNIQUE (opinion_id, review_id, list_id);

Answer 1

If you can identify a value that will never be stored in the referenced tables (e.g. a negative value), you can use a unique index that uses expressions:

create unique index 
  on favorite_comments( coalesce(opinion_id,-1), coalesce(review_id, -1), list_id );

the coalesce() will turn all null values in the same "real" value, forcing a unique constraint violation when you try to insert another NULL value.

The only drawback of a unique index compared to a unique constraint is, that the unique index can't be a target of a foreign key (while a unique constraint would be)