CODEWITHSUNDEEP
sqlpostgresqldatabase-designconstraints
Mahoni
Mahoni

Reputation: 7466

Model XOR check into unique constraint

I am modeling a reactions table with columns user_id, post_id, reaction. I want to have entries in this row to be unique which can be easily achieved with a unique index.

reactions can be like, dislike, viewed, applauded

Now the problem is, I also want to model a XOR relationship for like and dislike, meaning, when there is already a row with like for a given pst and user, it shoudn't be possible to add another row with dislike.

Since there are other optional reactions I don't know to write the constraint check for this. Is this even possible?

Upvotes: 0

Views: 281

Answers (1)

mu is too short
mu is too short

Reputation: 434785

You only want the (user_id, post_id) uniqueness to apply when the reaction is 'like' or 'dislike'. That's a partial index:

When the WHERE clause is present, a partial index is created. A partial index is an index that contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table. [...] Another possible application is to use WHERE with UNIQUE to enforce uniqueness over a subset of a table.

You want an index like this:

create unique index INDEX_NAME
on TABLE_NAME (user_id, post_id)
where reaction in ('like', 'dislike')

where you'd supply values for INDEX_NAME and TABLE_NAME of course.

Upvotes: 2

Related Questions