INSERT INTO a table with UNIQUE INDEX for two columns

Question

I have a simple tag_map table as

CREATE TABLE tag_map
(
tag_map_id mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
post_id mediumint(7) unsigned REFERENCES posts(post_id),
tag_id mediumint(7) unsigned REFERENCES tags(tag_id),
UNIQUE INDEX (post_id,tag_id),
PRIMARY KEY(tag_map_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

I added UNIQUE INDEX to avoid duplicate pairs of a tag associated to a post. Now when I try to add new enteries as

INSERT IGNORE INTO (post_id,tag_id) VALUES (post1_id,tag1_id), (post1_id, tag2_id),...

I will receive an error

ERROR 1062 (23000): Duplicate entry '16777215' for key 'PRIMARY'

but when I SELECT WHERE tag_map_id='16777215'; this belongs to a different tag and post.

Where did I wrong?

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

Your PK's mediumint max value of 16777215 has been reached.

Alter to int or above

Answer 2

tag_map_id is the primary key for the table and can't be duplicated. Check SELECT MAX(tag_map_id) FROM tag_map; and the value of AUTO_INCREMENT in SHOW CREATE TABLE tag_map;. The second value should be greater than the first. Sometimes these can get out of sync.

If the AUTO_INCREMENT value is less than the maximum tag_map_id, just do ALTER TABLEtag_mapAUTO_INCREMENT=x;where x is one greater than MAX(tag_map_id).

Answer 3

Your tag_map_id is declared as the table PRIMARY KEY. You may never, ever, ever have a duplicate primary key. It doesn't matter which tag and post the second row belongs to: it has the same primary key.

You will need to alter your schema if you want two items with the same tag_map_id, but it's more likely you've made a logic error in what you are trying to accomplish.