CODEWITHSUNDEEP
mysql
Eugeny89
Eugeny89

Reputation: 3731

Mysql: ALTER TABLE and foreign keys

I'm trying to do

ALTER table tbl_name Engine=MyISAM;

and I get 

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

How can I perform above query without deleting table having foreign key?

Thank you in advance!

UPD have I understood correctly that only MyIsam supports fulltext search?

Upvotes: 2

Views: 4013

Answers (2)

mmanzato
mmanzato

Reputation: 253

SET FOREIGN_KEY_CHECKS = 0; does not help.

Foreign keys must be dropped manually if there are other InnoDB tables containing foreign key references towards the table you are converting to MyISAM. See also this post on MySQL site.

Upvotes: 11

juergen d
juergen d

Reputation: 204766

try

SET FOREIGN_KEY_CHECKS = 0;
ALTER table tbl_name Engine=MyISAM;
SET FOREIGN_KEY_CHECKS = 1;

The InnoDB storage engine supports checking of foreign key constraints. For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

Foreign Key Differences

Upvotes: 4

Related Questions