asked on

Foreign Key Constraints fail dumping and restoring MySQL db

Hi all,

When I dump a MySQL db data to a .sql file from phpmyadmin and try to restore to the loca version of the db on my machine i get the following error;

Error Code 1452. Cannot add or update child row. a foreign key constraint fails.

Now I assume this is because it is trying to insert the data out of sequence (for example trying to insert a relationship table such as userole before user table is populated??)

Is there a way of clmbatting this when we dump the data?

Guy Hengel [angelIII / a3]

Either you create the constraints after the data loading, or you make indeed shure you load in the correct order. You might find that you can disable the constraints during the loading.

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

flynny

ASKER

ok temporarily releasing the constraints sounds the most straight forward idea.

How is it possible to do this using mysql workbench please?

johanntagle

You can type in that line and run it like any SQL statement.

But I suggest you make it part of the dump file, which you can open as an SQL script in Workbench or using by any text editor.

flynny

ASKER

ok could you provide the code to remove and then readd these constraints?

i.e. I assume I would use the code to remove constrainsts

Insert the data,

dump the constraints from the original db and re-add these back tot the copy db?

johanntagle

Why remove and readd when you can just disable and re-enable them with the commands I gave above?