Blog

MySQL and InnoDB Foreign Key Tip

If you are not a DBA, as is in my case, debugging SQL errors can get tricky and cryptic. An error message like

MySQL has left you

or

General error: 1005 Cant create table `./db_name/table_name.frm

wont mean much to you and trying to figure out the problem can be a pain. After some googling and messing around with my SQL statement, I now know some of the causes for the 1005 error. This error usually occurs if youre trying to create foreign key constraints in your table, so heres some of what you should look out for:

  • The referencing column and the referenced column are of the same type
  • The referencing column and the referenced column are of the same length
  • The referencing column and the referenced column are both signed or unsigned

basically the referencing column and the referenced column need to be identical. This may sound trivial, but its something you might not think about specially if there was a long period of time between when you created the first table and when youre creating the second one. Or if some one else on the development team created the table youre referencing and did not use the default values whereas you did.

  • When using a SQL dump to create the tables, the referencing table is created before the referenced table (this could happen when the dump is generated because the tables are sorted by table name)

to get passed that error add the following lines

SET FOREIGN_KEY_CHECKS = 0;

at the top of the dump and

SET FOREIGN_KEY_CHECKS = 1;

at the bottom of the dump.