Convert MyISAM to InnoDB where tables have two-column (composite) PK and one of those is auto-increment

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

I realize that we need to do away with the multi-column primary key in order to use auto-increment and InnoDB. We have thousands of records though and these records have relationships to other tables.

Are there any tips on how to convert these tables to InnoDB? The only method I've come up with is to first add a new column in each table, set it as the sole auto-increment primary key and then use scripts to update the dependent tables to point to the new (开发者_StackOverflowtruly unique) primary key.

Thanks Steve

In MySQL 5.0 and greater versions you can include the auto-increment column as part of the primary key, but it has to be the first field in the index.

*...An AUTO_INCREMENT column must appear as the first column in an index on an InnoDB table...*

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