2

I have never faced any issue like this, but I am just curious. Let's say I have a database dump file with me and I want to import the dump file to another database. There are 2 tables table_A and table_B. table_B contains a primary key and table_A contains the foreign key. And serially, table_A is first and then table_B.

So, when I update the database table_A will be created with a foreign key and next table_B will be created that contains the primary key.

So, will there be any error in this case?

  • Which error message? And please show SHOW CREATE TABLE for both tables. – Rick James Nov 28 '18 at 16:42
  • Hi rick, i haven't faced any problem like this, i am just curious that if i get a database as mentioned in the question, will there be any issues while restoring it ? – swogat Dec 4 '18 at 10:26
  • If the data was 'consistent' before, then there should be no errors during the load. There are probably ways to create inconsistencies and leave them unchecked. (See techniques for disabling/enabling FOREIGN KEYs.) – Rick James Dec 4 '18 at 17:40
2

Depending on the type of backup, that can indeed happen. It's therefore recommended to disable foreign key checks before the restore, e.g. with

SET FOREIGN_KEY_CHECKS=0;

and re-enabling them afterwards with

SET FOREIGN_KEY_CHECKS=1;

This has another advantage: MySQL won't check the foreign key for every single imported record, but for all records at once. This will speed up your restore.

See Disabling foreign key checks on the command line on Stack Overflow.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.