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.

Other available options and arguments for ALTERING TABLES can be found here ..



27 thoughts on “MySQL – Temporarily disable Foreign Key Checks or Constraints

  1. Make sure you absolutely know what you are doing if you are temporarily disabling the foreign keys. That may be a bad habit to get into. If you make a mistake while deleting there could be no going back.

    I would suggest spending the time to write an actual script to delete the records from the child up to the parent. This is the whole reason for the referential checks on integrity.

    Do as I say, not as I do: thanks for the tip.

    1. Easy to be a critic, but when using foreign keys building reverse cascading constraints, it is often a requirement to disable FOREIGN_KEY_CHECKS until all tables are written, when doing so you use START TRANSACTION with commit and/or rollback. Then makes it safe providing your script handles failures well. Certainly a bit of code to pay attention to and test heavily from the start.

    2. Ahhh yes, so much rope. Foreign keys exist largely to keep folks from hanging themselves and generally if you need to turn them off you are doing something wrong. Restores and backups might be the possible exception but generally the folks doing that work know what there doing. I’d say, look once, twice, three times at the order at which you are adding and deleting records and consider those error messages your friend, not your enemy. If you have to turn off a check to update data, chances are your actions have a high risk of causing data corruption.

  2. One useful use-case for this is when you’re restoring data from a backup or doing an import. mysqldump outputs tables in alphabetical order, which means when you go to restore the dump, it’ll just run through all the tables in that order.

    But that is often the wrong order when considering foreign keys and so on. So usually what I do is stick “SET foreign_key_checks = 0;” at the top of my dump, then do the import.

  3. I am part of a group of volunteers that is starting a new scheme in our neighbourhood in Denver. One of the community projects that we are going to to initiate relatates to your blog, and therefor some of the information here is of value for us and I just wanted so say thank you for that.

  4. This is very useful when you’re creating a script for backup your database.
    When using the statement “SHOW CREATE TABLE” to dump your tables , it include the CONSTRAINS and generate a error when you try to restore because of the tables order, here is where disabling the foreing checks it’s helpful.

  5. oh …you saved me…I updated one row in production database by mistake but
    SET foreign_key_checks = 0;
    worked for me 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s