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.
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.