[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1422
  • Last Modified:

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?
0
flynny
Asked:
flynny
  • 3
  • 2
1 Solution
 
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
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now