MySQL: issue setting unique restraint on combination of rows

Question

I'm trying to set a unique restraint on a combination of columns rather than on a single column. I have a table, "tags":

id (int, PK, AI, unsigned)
tag (varchar 25)
user_id (int, unsigned)

Following the answer to this question, I tried to set the combo restraint via:

ALTER TABLE `tags` ADD UNIQUE `unique_tag_user_combo` (`tag`, `user_id`);

So far, so good. But when I come to test it, by seeing if it will let me insert the same tag twice but with different user IDs (it should), it errors:

INSERT INTO `tags` VALUES (NULL, 'foo', '1'), (NULL, 'foo', '2')

...throws...

Duplicate entry 'foo' for key 'name_2'

Remember the unique restraint is on the combo of tag + user_id, so this query, to my mind, should run fine. I could understand this error if I'd tried to insert foo/1 twice, but not foo/1 and foo/2. What am I missing?

(EDIT - also, what's that 'name_2' reference in the error message all about? I don't have a column with that name...)

Have you checked if your table is empty before inserting those values? — Mihai, Jun 25 '14 at 11:08
Damn... there's already a unique instruction on it, you were right. If you post that comment up as the answer I'll accept it. Thank you. — Mitya, Jun 25 '14 at 11:12

score 1 · Accepted Answer · answered Jun 25 '14 at 11:13

1

You might also have an unique index on tag column.Use

SHOW CREATE TABLE yourtb

answered Jun 25 '14 at 11:13

Mihai

26,325
7
66
81

MySQL: issue setting unique restraint on combination of rows

1 Answers1