MySQL Foreign Key Tip

If you use MySQL Foreign Keys long enough, eventually you'll get the error:

 MySQL Error Number 1005 Can’t create table  ‘.\XXX\YYY.frm’ (errno: 150) 

I fairly quickly learned that this error specifically means there's a problem with your foreign key constraint. The problem is, this is a totally generic error message - there could be any number of things wrong , and this message won't help you figure out what the problem is. (Like when you wife you gives you That Look, yet you have no idea which of the dozens of things you may or may not have done wrong, are the cause of said look.)

While trying to debug an especially tricky case of the Error Number 1005's (the problem: the ID type in the foreign table was INT, the constraint tried to match it up to a BIGINT column - d'oh!) - I learned the following:

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to errno 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

So, to figure out my issue, I just needed to run:

  SHOW ENGINE INNODB STATUS 

Hmmm, reading the manual - that's just crazy enough to work!