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.