There I was, coding up a fresh Doctrine schema for a Symfony app, when my plans to implement foreign keys were repeatedly foiled:
SQLSTATE[HY000]: General error: 1005 Can’t create table ‘./mydb/#sql-xxxx.frm’ (errno: 150). Failing Query: ALTER TABLE cow ADD FOREIGN KEY (barn_id) REFERENCES barn(id)
I was using InnoDB, both tables were there, both columns were there, what else could MySQL want? Why didn’t it work?
Apparently, you must use the same data type for both columns. My cows.barn_id was an INT, while barns.id was a SMALLINT. This didn’t fly. Changing both to SMALLINT did the trick.
Hint: if you’re using Symfony with Doctrine, like I am, use “integer(4)” to get the MySQL INT datatype, or “integer(2)” for a SMALLINT data type. Plain old “integer” converts to BIGINT.