MySQL foreign key problem

Question

A client of mine recently formatted his machine and re-installed MySQL Server and my application. He complained that deleting records from master table is not affecting the child tables. I requested him to send the backup of the database. When I restored the database, I found that the Table Engine has changed to MyISAM whereas they were set to InnoDB.

I deleted the records from the child table that were absent in the primary table. After this when I am not re-setting the Foreign Key Index, it displays error: "Foreign key contraint failed. Error 1005" and sometimes error: 150.

I have double checked the rows that might be left in either the primary table or in the child table, but nothing seems to be working.

The primary table has two columns that combinedly form a Primary Key. The columns are: BillNo, BillDate.

Please assist.

Answer 1

This is a widely known MySQL pitfall; I have hit this problem a few times myself. They probably had some problem with InnoDB, and restored their database from backups. Since InnoDB wasn't working, it fell back to the MyISAM storage engine which doesn't support integrity constraints (like foreign keys).

Basically the problem is that, if the InnoDB engine fails to start for whatever reason (usually configuration problems) -- then MySQL silently falls back to the MyISAM engine. Even if your statement says:

CREATE TABLE () ENGINE=InnoDB

then, if InnoDB isn't active, MySQL will happily create a MyISAM table without even warning you. Bye-bye data integrity! :)

You can run SHOW ENGINES to see which engines are active. See this MySQL bug report for more details.

Answer 2

1. Check that you're using InnoDB engine for both tables.

2. Check that both fields are of the same type and that they are indexed.

From http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html:

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.