3

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

1
  • I need to do some more checks but it seems to be working, but i really dont understand the logic behind since for example this review_id= NULL, list_id = 4 its not going to be unique if you look this two columns in an isolated way.
    – Imanol Y.
    Nov 5, 2019 at 12:42

1 Answer 1

7

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)

2
  • Very clever solution, its common to use this kind of "computed" constraints?
    – Imanol Y.
    Nov 5, 2019 at 14:07
  • 1
    @ImanolY.: I don't know how "common" this is, but I have used it occasionally in Postgres and Oracle.
    – user1822
    Nov 5, 2019 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.