InnoDB Foreign Key Error 150

As part of created and modifying the database structure of the various products here at work I’ve encountered numerous times issues with creating foreign keys. Finally I had a deeper look into what may be causing the problem, despite having (or so I thought) the exact same field types in the tables.

Using SHOW ENGINE INNODB STATUS; I was able to see more detailed information about the error. And the information was as follows:

130312 11:47:36 Error in foreign key constraint of table <database_name>/#sql-3a6_3faa:
FOREIGN KEY (`customer_code`) REFERENCES `customer` (`customer_code`) ON UPDATE NO ACTION ON DELETE CASCADE:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Which made me realise that I’d not been creating the new tables with the correct collation, utf8_general_ci, which I had been using for all of the previous tables. Even after doing this I was still having problems. Although I didn’t realise (I know now), individual columns can have their own collation. It turned out the foreign key field I was trying to link on was different from the one I was linking from. After making this change then the foreign key was created as expected.

Advertisements

Published by:

Skyrail

Production Facilitator at Audacious Church Previously Software Developer at UKFast Ltd. and Web Applications Developer at SenseLogix Ltd. Graduate of Renewable Energy BSc from the University of Exeter. Experience with PHP, MySQL, Javascript, CSS and HTML.

Categories SQLTags Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s