"A foreign key constraint fails" when converting a table from InnoDB to MyISAM

Question

For some reason, the MySQL tables in my Django web application are using different formats, and it is causing foreign key constraint errors (like in this question).

Here is what the table formats look like.

SHOW TABLE STATUS WHERE `Name` in ('') ...
+----------------------------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------
| Name                       | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          
+----------------------------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------
| table_0                    | MyISAM |      10 | Dynamic    |    341 |             83 |       28472 |  281474976710655 |        19456 |         0 |            346 | 2013-09-05 15:52:11 | 2014-01-10 14:17:18 | 2013-09-05 15:52:11 
| table_1                    | MyISAM |      10 | Dynamic    |  66422 |             49 |     3297260 |  281474976710655 |      2025472 |         0 |         141146 | 2013-07-20 19:12:24 | 2014-01-10 19:20:21 | 2013-07-20 19:12:27 
| table_2                    | MyISAM |      10 | Dynamic    |      3 |             53 |         160 |  281474976710655 |         3072 |         0 |              4 | 2013-07-20 19:12:27 | 2013-07-20 19:12:27 | 2013-07-20 19:12:27 
| table_3                    | MyISAM |      10 | Dynamic    |      8 |             99 |         796 |  281474976710655 |         3072 |         0 |             10 | 2013-07-20 19:12:27 | 2013-07-20 19:12:27 | 2013-07-20 19:12:27 
| table_4                    | MyISAM |      10 | Dynamic    |      0 |              0 |           0 |  281474976710655 |         1024 |         0 |              1 | 2013-07-20 19:12:27 | 2013-07-20 19:12:27 | 2013-07-20 19:12:27 
| table_5                    | InnoDB |      10 | Compact    |      0 |              0 |       16384 |                0 |        16384 |   7340032 |              1 | 2013-11-20 14:04:02 | NULL                | NULL                
| table_6                    | InnoDB |      10 | Compact    |      0 |              0 |       16384 |                0 |        16384 |   7340032 |              1 | 2013-11-20 14:03:39 | NULL                | NULL                
| table_7                    | MyISAM |      10 | Dynamic    |    860 |            125 |      107624 |  281474976710655 |        25600 |         0 |            977 | 2013-11-20 14:04:35 | 2014-01-06 18:32:57 | 2013-11-20 14:04:35 
| table_8                    | MyISAM |      10 | Fixed      |   6632 |             21 |      139272 | 5910974510923775 |       173056 |         0 |           6753 | 2013-07-20 19:16:47 | 2014-01-09 11:26:36 | 2013-07-20 19:16:47

I tried to update the table formats as described in this question. But received the following error.

mysql> SET FOREIGN_KEY_CHECKS=0; 
mysql> alter table ztrap_emailreportoptin engine = MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

How can I go about making the engine consistent between tables?

(Also, any idea how this happens to begin with?)

score 4 · Accepted Answer · answered Jan 14 '14 at 11:15

MyISAM does not support foreign keys. To convert to MyISAM, you would need to drop drop all foreign key contraints first.

Your error message tells you that your table ztrap_emailreportoptin either references another table, or is referenced by another table.

You can list such references with:

SELECT
    table_name, column_name, constraint_name,
    referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL;

However, I would recommend converting your tables to InnoDB instead.

This worked and the explanation makes sense. Thanks. – bbrame Jan 14 '14 at 14:33 — bbrame, Jan 14 '14 at 14:33

score -2 · Answer 2 · answered Jan 11 '14 at 02:15

-2

Try converting MyISAM ones to InnoDB, eg

alter table ztrap_emailreportoptin engine = InnoDB;

This way you'll have the server to enforce the foreign key checks on all tables.

answered Jan 11 '14 at 02:15

Andy W

2,082
1
13
9

Andy, that table already uses InnoDB. The rest of the tables use MyISAM and I'd like to make them all MyISAM. – bbrame Jan 11 '14 at 22:10

"A foreign key constraint fails" when converting a table from InnoDB to MyISAM

2 Answers2