4

I exported all tables of my database. I opened the generated file then I saw that phpMyAdmin sorts the queries by table_name. So there is potential foreign key issues because master table is created/inserted after detail table! So how to disable foreign key checks when exporting table data with phpMyAdmin, and how to re-enable them at the end of the script?

Misa Lazovic
  • 2,805
  • 10
  • 32
  • 38
pheromix
  • 18,213
  • 29
  • 88
  • 158

4 Answers4

3

Just disable foreign key checks before and re-enable them after you execute your script:

SET foreign_key_checks = 0;
-- run some queries
SET foreign_key_checks = 1;
Misa Lazovic
  • 2,805
  • 10
  • 32
  • 38
3

From the Export tab, select the "Custom" export method.

enter image description here

In the "Format-specific options:" area, look for and check "Disable foreign key checks".

enter image description here

It does the same thing Misa Lazovic suggested to do, but since you're using phpMyAdmin to do the export this is the graphical way of adding those lines.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
  • Do you have any idea about to create batch script for doing same task? – ersks Jul 29 '18 at 10:43
  • @ersks Since phpMyAdmin is a graphical tool meant to be used interactively by a person, it's not well equipped for scripted or batch processing. You'll have better luck with the 'mysqldump' command line program for such purposes. – Isaac Bennetch Jul 29 '18 at 14:45
  • I have written MS-DOS Code to dump the database, but I need to disable foreign key check before dumping from the database and enable that after a successful dump. I need to disable/enable fk for the next import of that database. – ersks Jul 30 '18 at 07:14
  • @ersks According to https://stackoverflow.com/a/2429723/2385479 and https://stackoverflow.com/a/11004245/2385479, that's the default with any recent version of mysqldump. If you're seeing different behavior, you should probably open a new question about it (if you do, you can mention it here so I and others can find it). – Isaac Bennetch Jul 30 '18 at 15:19
1

I also faced the same issue for importing the database in server. And tried all answers above but couldn't figure out. This tutorial fixed my issue and made me able to import my DB in phpmyadmin. Tutorial

Hope this one helped!

Lalarukh khan
  • 95
  • 7
0

When creating the foreign key you should add this option : 

 ON DELETE CASCADE  Or 
On delete set  null

So when you will delete the foreign key, there will be no PB.

Sdmg15
  • 19
  • 7