The MySQL 5.0 reference manual has very specific guidelines as to how auto incrementing is handled within the InnoDB storage engine. This can cause problems for individuals who have improperly created their tables. Perhaps you have made the following mistake and didn’t catch the error. I’ll demonstrate a simple way to create a table utilizing a composite key which will effectively trigger ON DUPLICATE KEY UPDATE on an INSERT statement.
I ran into a small snag triggering ON DUPLICATE KEY UPDATE in the event an INSERT occurred with a match for a composite key. Suppose I receive a Flickr API response regarding photo albums I have created. I would receive data as stated in this specification. For this example I will store the response data as follows:
CREATE TABLE photosets ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, photoset_id VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci, title VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci, description TINYTEXT CHARACTER SET utf8 COLLATE utf8_general_ci, PRIMARY KEY (photoset_id), UNIQUE KEY (id) ) ENGINE=InnoDb CHARACTER SET utf8 COLLATE utf8_general_ci;
Creating your table in such a manner will save you from the following error had you chosen not to include the UNIQUE KEY (id) line:
It will also correctly obey the following INSERT statements by utilizing ON DUPLICATE KEY UPDATE for the second INSERT:
INSERT INTO photosets SET photoset_id = 5, title = "First Title", description = "First Description" ON DUPLICATE KEY UPDATE title = "First Title", description = "First Description"; # second entry triggering an update INSERT INTO photosets SET photoset_id = 5, title = "First Title", description = "Second Description" ON DUPLICATE KEY UPDATE title = "First Title", description = "Second Description";
The problem with creating a composite primary key as opposed to a separate primary and unique key is that ON DUPLICATE KEY will not fire because the MySQL reference specifications state InnoDB handles auto incrementing in a very specific manner.
Interesting… Why did you not make id your primary key? The difference is that the primary key is copied to all other indexes in innodb, and a varchar(50) field is bigger than an unsigned int. Minor point: a composite primary key usually implies that neither field alone is unique, but in your case it looks like you have two keys which are unique on their own. For example, if you had PRIMARY KEY (id, photoset_id), it would only count as a duplicate if you inserted a row that matched on both values – having a composite primary key is not the same as having two separate unique indexes, just like UNIQUE(id), UNIQUE(photoset_id) is not the same as UNIQUE(id, photoset_id).
The choice to store an auto incrementing key is that I have always considered it good practice to store auto incrementing keys for nearly all tables (with the exception of pivot tables) because it allows for easier migrations in the event my site’s overall table structure need to undergo a drastic change. To be quite honest, looking back at the code, I can’t actually pinpoint why I had decided to not use a composite key with a leftmost prefix of photosets.photoset_id in favor of photosets.id. If I had to do it all over again, I probably would have gone with PRIMARY KEY(photoset_id, id). Hindsight is always great though, right?