Switching off 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)