in Databases, MySQL

MySQL – Temporarily disable foreign key checks or constraints


Databases are all about saving data. With DBMS and RDBMS, the entire data became relational and all the records became related to each other as in the real world. So came into existence the concepts of primary keys, foreign keys, foreign key constraints and whole bunch of other terms like composite keys, referential integrity, indexes and what not.

So coming back to the objective of pinning down this post, some days ago I came across the requirement of deleting some user records from the user table. As soon as I tried deleting the records, I came across the error of referential integrity where in I was greeted with the error that child records were there and hence the delete operation was not allowed.

So I searched for a shortcut which could let me do my task. And I came across this …

SET foreign_key_checks = 0;
DELETE FROM users where id > 45;
SET foreign_key_checks = 1;

By setting the foreign key check to 0, I was able to update / delete my users table. Once I was done with my operations on the user table, I reset the key check to 1 again and everything is back in place now.

You can always drop a FOREIGN KEY the usual way ..

ALTER TABLE users DROP FOREIGN KEY <foreign_key_name>

I disabled the foreign key checks for some requirement of mine working on a test data set. It is absolutely important to think about why you are doing this and its repercussions. This could be used to bring some of your DB records in the correct state but should not become a part of your daily trouble-shooting. Foreign key constraints exist to enforce referential data integrity. In most cases, if you are trying to remove records, it is a good practice to process child records first and then go for the parents. The sole purpose of this article is to make you aware of the option which disables the foreign key constraints. Once done, do not forget to go back to the foreign key constrained world of data.