How to avoid MySQL to check foreign keys when creating a database from a dump script?

Question

I have a MySQL dump file and I want to create a database from it. It's a very large file (1.3 GB) and I know it will take a lot of time to be ready.

But I'm having problem with the constraint check, because when it attempt to create a table, it cannot found the parent table. I added a SET FOREIGN_KEY_CHECKS = 0 at the beginning of the script and a SET FOREIGN_KEY_CHECKS = 1 at the end, but it still doesn't work. I get a ERROR 1005 (HY000).

Why is this happening? Shouldn't SET FOREIGN_KEY_CHECKS = 0 tell MySQL to avoid checking if the parent table exists? What can I do? This script creates 158 tables and I cannot change the order to avoid this error, the file has over 10,000,000 lines!

Any suggestions? Thanks a lot!

Answer 1

Check that the database name are correct, this is usually a problem in the foreign keys, because they include the name of the database.



If your file is too large, then you can use sed 's/old_db_name/new_db_name/g' db.sql >> db.fixed.sql

Answer 2

How about moving the statements that create the foreign key's to the end of the script.

Answer 3

There could be purely one 'typical key' in a table. try, CREATE table VIOLATIONCITATION (ViolationCitationID Int typical KEY, VIOLATIONID CHAR(5) REFERENCES VIOLATION(VIOLATIONID), CITATIONID CHAR(5) REFERENCES quotation(CITATIONID), FINECHARGED DECIMAL(5,2) );