Referential integrity problem upon migration from myisam to innodb

Question

I want to migrate one of my database from myisam to innodb. The main reason is to allow my application to honor referential integrity.

This is what I did

1. Exported the current myisam database
2. Dropped the database
3. Switched to innodb database http://parasjain.net/2010/06/08/how-to-switch-to-innodb-database-in-mysql/
4. Verified the engine switch, then created the new database

The referential integrity works now. For example, if I create Parent p1 and Child c1 and refer p1 in c1, I can't delete P1 as long as there is a child dependent on it.

So far so good

But the problem occurs when I import the old data from the exported dump

The problem is that the Referential Integrity is honored for the new data. But in my existing data if there is any relationship like p2 referred by c2 then I can delete p2 breaking the referential integrity. c2 is orphan now.

Is there any way of repairing/re-indexing the tables so that referential integrity is enforced on the existing data too?

Answer 1

Make sure you have fresh indexes on all your foreign keys and referenced keys (table indexes are updated on every insert) and then run

ALTER TABLE you_table0 ADD FOREIGN KEY ...

for each table, with all of your legacy data already in place.

Should work )

Answer 2

Thanks for your answers. That would have been useful because that would have led me to the root cause. The root cause of the problem is that my mysqldump was containing drop and create statements. The create statements were having ENGINE=MYISAM since at the time of export this was the engine.

I created new dump using --no-create-info option and everything worked just fine.