# Enforcing a constraint that a user may vote on each post at most once

I have the following table in a PostgreSQL database:

    creator uuid not null,
post    uuid not null,
type    text not null,
content text not null,
constraint check_type check
(type = 'comment' or
(type = 'vote' and
(content = 'up' or
content = 'down')))


In order to enforce the constraint that there is no duplicate pair of (creator, post, type) where type = 'vote' (ie. a specific user can only vote on a specific post once) I can either create a unique index like so:

create unique index interactions_index on posts.interactions
(creator, post, type, content) where type = 'vote';


Or I can query the table on in the server code and do a check there.

My questions:

1. which one is more efficient, or should I include both for safeguards? How much will having a unique index impact performance (say 100,000 posts @ 1000 likes/post = 100,000,000 database lines)?

2. I am using this to store "interactions" with a post (ie. likes, comments), is this a good/sustainable way to approach this, or should I be doing something different?

• What is the difference between type='like' and type='vote'? Sep 6 '19 at 5:04
• none. it's a typo :) Sep 6 '19 at 22:35