转换为MyISAM，`foreign_key_checks = 0`不工作

Question

I'm working on some scripts to programmatically automate a database migration, and at the end of a long series of compromises working in a system I didn't design, own, or maintain, I need to convert a number of MySQL InnoDB tables to MyISAM tables. However, when I attempt to alter a table

mysql> ALTER TABLE catalog_category_entity ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>

MySQL complains. This is expected. However, if I disable the foreign_key_checks, I get the same results

mysql> SET foreign_key_checks = 0;
mysql> ALTER TABLE catalog_category_entity ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This happens regardless of my setting the key check at the global or session level. I assume the problem here is the table in questions has other InnoDB table which reference it, and MySQL refuses to end up in a state where an InnoDB table references a table that's not InnoDB. (I may be incorrect on this, and I'm more than happy to be corrected)

Is there a quick way to handle this situation? I basically want all tables in the database to be MyISAM, and tracing out all the relationships myself and/or manually removing the contraints seems very time consuming, and the sort of thing that a computer would be better at.

If the answer is "suck it up and do the work" I'm happy to hear that from an expert — I just don't want to waste the time if I don't need to.

If it matters the language I'm programming in in PHP, but I'm happy for solutions that require other languages.

Answer 1

MyISAM doesn't support foreign keys. Delete the keys before changing the engine:

> ALTER TABLE catalog_category_entity DROP FOREIGN KEY fk_name;
> ALTER TABLE catalog_category_entity ENGINE=MyISAM;

You can find the foreign key name by calling:

> SHOW CREATE TABLE catalog_category_entity;

This may be helpful: Delete all foreign keys in database(MySql)