dousui3124
dousui3124
2014-07-14 19:15

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

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongluedeng1524 dongluedeng1524 7年前

    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)

    点赞 评论 复制链接分享