Export and import data from PHPMYADMIN

Question

I exported the data using phpMyAdmin, but when I import the data, I get this error:

# 1452 - Unable to add or update child row: foreign key constraint is completed

I can set the data accordingly and then I will not get an error. But is there a better way to do this? Something like disabling some parameters in phpMyAdmin or adding some query in SQL?

Answer 1

The problem is that pma does not care about the order for the insert lines. so the table table-row is inserted with FK, where the FK row has not yet been imported.

To resolve this issue, select the Disable Foreign Key Checks check box when exporting from PhpMyadmin. Or install it yourself:

 SET FOREIGN_KEY_CHECKS=0; 

and in the end:

 SET FOREIGN_KEY_CHECKS=1; 

Answer 2

This error ( Cannot add or update a child row: a foreign key constraint fails ) is reported in MySQL FK Doc

To add a link between two tables, the condition must match existing data.

This means that if you say table1.id = table2.id , then all identifiers table1 and table2 must match.

To solve this problem, you need to eliminate or fix those lines that do not match.
Example:

 table1.id | table2.fk 1 | 1 ok 2 | null error 3 | 4 error if id 4 is not in table1