Postgres Column Value Unique to User

March 6, 2023

In my app I have users and tags. Tags have a name property (tag.name).

I want to enforce unique tag names per user. Meaning a user cannot have two matching strings in the name column. Tag name uniqueness is only account specific. Meaning user 1 and user 2 can both have a tag.name equal to Tag1

I only know of two ways to do this. Before saving, fetch all the tags for the user and make sure the incoming tag names do not create duplicates. Or, create a new column that is a combination of the user.id + tag.name aka 1-tag1 and set the column as UNIQUE.

Is there a better way to do this? If not, which of my two strategies seems best?

>Solution :

CREATE TABLE table_name
      (
               id INTEGER NOT NULL,
               tag_name VARCHAR(20) NOT NULL,
               PRIMARY KEY(id),
               CONSTRAINT some_name UNIQUE (id, tag_name)
      );

you can also make the two columns together as a primary key by doing this:

PRIMARY KEY(id, tag_name)

but I don’t like that approach personally. I prefer primary keys to be the integer ID columns to use auto-increment and other fancy features without much configs.