Switching off referential integrity in MySQL

A tutorial on how to disable foreign key referential integrity in MySQL.

If you are loading very large quantities of data into MySQL, it sometimes makes sense to switch off foreign key constraints to enable the data to load faster. There are two commands for doing this:

mysql> SET foreign_key_checks = 0;

That will switch off foreign key checks for the current MySQL session, and:

mysql> SET GLOBAL foreign_key_checks = 0;

That will switch off foreign key checks at a MySQL server level.

Finally, to check the current setting of foreign_key_checks use this command:

mysql> SHOW Variables WHERE Variable_name='foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

John Collins

I have been writing about web technology and software development since 2001. I am the developer of the Alpha Framework for PHP, and the five.today personal productivity app. I love open source, technology, and economics.