MySQL "Error 1005" when adding tables

Question

I've recently been working with a MySQL database, and using MySQL workbench to design the Database.

When I use the export to SQL function, so I can actually get the layout in to the Database, I get:

"Error 1005: Cannot create table"

This appears to be related to Foreign Keys in the create table statement.

Does anybody have a work around for this that doesn't involve taking the constraints out and putting them back in later? That's a less than ideal solution given the size of the database.

Answer 1

When you get this (and other errors out of the InnoDB engine) issue:

SHOW ENGINE INNODB STATUS;

It will give a more detailed reason why the operation couldn't be completed. Make sure to run that from something that'll allow you to scroll or copy the data, as the response is quite long.

Answer 2

I ran into this situation recently when I attempted (in InnoDB tables) to make a foreign key reference to a column that had a different data type.

MySQL 5.1 Documentation