Convert MyISAM to InnoDB, where the tables have a two-column (composite) PK, and one of them is an automatic increment

Question

I would like to convert some of our MyISAM tables to InnoDB so that I can take advantage of foreign key support. However, most tables use a two-column (composite) primary key, in which one of these columns is an automatic increment (this was done for historical reasons, and also guaranteed that an auto-increment column could act as a kind of incremental key for records in the context of another column value)

I understand that we need to get rid of the primary key with multiple columns in order to use auto-increment and InnoDB. We have thousands of records, although these records are related to other tables.

Are there any tips on how to convert these tables to InnoDB? The only method I came up with was to first add a new column to each table, set it as the only primary auto-increment key, and then use scripts to update the dependent tables to point to a new (truly unique) primary key.

Thanks Steve

Answer 1

In MySQL 5.0 and later, you can include the auto-increment column as part of the primary key, but it should be the first field of the index.

* ... The AUTO_INCREMENT column should appear as the first column in the index of the InnoDB table ... *

Check the documentation entry here http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html