A foreign key constraint fails

Today I ran into this problem for the second time in three years… so it took me a while to figure out the solution (with the help of my trusty partners here in the office of course).

It’s quite a common error message :

SQL Error: 1452: Cannot add or update a child row: a foreign key constraint fails

Usually the foreign key data in the query is either blank or references a non-existing record, but in my case everything was fine with the data. Stranger yet, the same code worked on my localhost and only threw this error on the live server.
First I looked at the query itself, which was fine. Then I compared the foreign key definitions with the one in the parent table, and it was identical. After that I asked for some suggestions, and somebody provided the correct answer.

So to put you out of your misery here it is: the server’s default engine was set to MyISAM instead of InnoDB. The table I was working with (which contained the foreign key reference to another table) was correctly set to InnoDB, but the referenced table itself was not set to InnoDB but had defaulted to MyISAM instead. This caused the error since MyISAM tables do not support foreign key constraints.

To create a table with the InnoDB engine, simply put this bit at the end of your table creation script :

CREATE TABLE mytable (
    id        INTEGER     UNSIGNED    NOT NULL auto_increment PRIMARY KEY ,
    `name`    VARCHAR(30) NOT NULL
) ENGINE=InnoDB;

Sneaky little fellar…

The default engine type for a MySQL database is MyISAM (but can be changed), so to prevent errors like these it’s always a good idea to specify the table engine type for each table in your creation script.

2 Responses to “A foreign key constraint fails”

  1. Mike says:

    Note that as of MySQL 5.5 InnoDB is the default storage engine.

  2. vadim says:

    I have the same problem, but both tables defined explicitly in InnoDb and data type and values are correct. This never happened in 5.1. version, but now seems to happen randomly for no reason. Any suggestions? Thanks….

Leave a Reply to vadim