What's the safest way to convert table with InnoDB to MyISAM?

Question

My database is currently using the InnoDB engine. Now I want to add the fulltext search feature, which is why I want to convert my tables to MyISAM. But doing so breaks all foreign keys. How can I change my table engines to MyISAM safely?

How can I use SELECT...JOIN after I change my tables to the MyISAM engine?

ALTER TABLE jobs ENGINE = MyISAM;
Cannot delete or update a parent row: a foreign key constraint fails

Empress

Answer 1

I'd recommend you to do a dump of the db, change all the text from that file from InnoDB to MyISAM, then load the modified file

Susie

Answer 2

As I know, MyISAM doesn't supports foreign keys (compare the features offered by InnoDB vs the features of MyISAM). MySQL tries to tell you that you have to drop every foreign key constraint that references your jobs table before changing its engine to MyISAM.

Loesceke

I had the same error message converting a table and as soon as I deleted all the foreign keys constraints, the conversion worked perfectly. So it's exactly as Kohanyi says! (please think about what you're doing before deleting them...in my case, the constraints were useless, but in some cases they may be there for a reason (to guarantee DB integrity if some applications are bugged?) ;-) – Olericulture