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);
Best Answer
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:
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)