One of the MySQL databases I work with has a couple of hundred INNODB tables set up with foreign key constraints. I needed to copy the database using mysqldump to export data from one server and then load that into another but kept getting foreign key constraint errors. This post looks at how to load data from a file with MySQL and ignore foreign key constraints.
Foreign key constraints ensure database integrity by not allowing data to be inserted into a table if a matching key does not exist in another table, for fields set up in this way. However they can cause issues when trying to load a database backup into a database.
When attempting the load the backup (done by dumping into a tab delimited file – I’ve covered this previously in my using mysqldump to save data to CSV files post) I got foreign key constraint errors. I then attempted to re-arrange the order in which the tables were loaded but continued to get errors due to some tables having parent-child relationships in the same table. I would then have had to re-export the original data, ordering it to ensure no issues when loading it, and load all tables in a specific order.
This would all have been too hard and time consuming so it was simpler to import the data ignoring foreign key constraints; as long as all data was loaded then it would have integrity because the source database it. And any subseqeuent queries run on the database would be subject to the constraints.
The solution is to switch off foreign key constraint checking before loading the data and then switching it back on again after loading the data. To switch off foreign key constraint checking run the following SQL query:
SET FOREIGN_KEY_CHECKS = 0;
And to switch it back on again:
SET FOREIGN_KEY_CHECKS = 1;
The database dump I had done was each table into a tab delimited text file. I would normally load these in using the mysqlimport command line utility but in this case would not be able to run the SET FOREIGN_KEY_CHECKS query. However it is possible to load data from a text file into MySQL using the LOAD DATA INFILE command like so:
SET FOREIGN_KEY_CHECKS = 0; LOAD DATA INFILE '/path/to/mytable.txt' INTO TABLE mytable; SET FOREIGN_KEY_CHECKS = 1;
The LOAD DATA INFILE line would then need to be repeated for each file that needs to be loaded.
If the data had been dumped as a SQL query (which is the default when using mysqldump) it can be loaded using SOURCE by using the following query:
SET FOREIGN_KEY_CHECKS = 0; SOURCE myfile.sql; SET FOREIGN_KEY_CHECKS = 1;
These commands above were done using the the mysql command line, but it should also be possible to run them from within a programming language such as PHP and possibly even using an interface such as phpMyAdmin and the MySQL GUI tools.
Script to generate the SQL
I have followed this post up with a second one showing how to generate the SQL for multiple tables using a BASH shell script.