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?
flynnyAsked:
Who is Participating?
 
johanntagleCommented:
Open the dump file with a text editor and put this somewhere at the top:

SET FOREIGN_KEY_CHECKS=0;

Remember to do a SET FOREIGN_KEY_CHECKS=1; after.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
flynnyAuthor Commented:
ok temporarily releasing the constraints sounds the most straight forward idea.

How is it possible to do this using mysql workbench please?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
johanntagleCommented:
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.
0
 
flynnyAuthor Commented:
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?
0
 
johanntagleCommented:
Why remove and readd when you can just disable and re-enable them with the commands I gave above?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.