disable foreign key constraint

How to Temporarily Disable Foreign Key Constraint in MySQL

By default, MySQL checks for foreign key constraints before deleting a column value or truncating a table. But sometimes you may need to disable this constraint in order to delete one or more column values. In this case, you will need to temporarily disable it. Here are the different ways to temporarily disable foreign key constraint in MySQL


How to Temporarily Disable Foreign Key Constraint in MySQL

There are various ways to temporarily disable foreign key constraint in MySQL.


1. Using FOREIGN_KEY_CHECKS

Log into MySQL and run the following command to disable foreign key checks across all databases & tables.

SET FOREIGN_KEY_CHECKS=0;

After you have performed the required database operations, run the following command to re-enable it.

SET FOREIGN_KEY_CHECKS=1;

If you are programmatically running SQL delete queries that require you to disable foreign key constraints, then run the first query above before you run your delete queries to disable checks, and run the second query above, after you run your delete queries to re-enable them.


2. Using DISABLE KEYS

If you only want to disable foreign key constraint for specific tables, then you may use DISABLE_KEYS statement as shown below. Replace table_name below with the table name for which you do not want foreign key checks.

ALTER TABLE table_name DISABLE KEYS;

After you are done, run the following command to re-enable foreign key checks.

ALTER TABLE table_name ENABLE KEYS;


3. Permanently disable foreign key check

You may also permanently disable foreign key such that on deletion the foreign key in other dependant tables will be set to NULL. Here is an example. Let us say you have two tables table1 and table2 with foreign keys pointing to each other. In this case, you need to drop the existing foreign key constraints, using the DROP FOREIGN KEY commands, as shown below.

ALTER TABLE table1 DROP FOREIGN KEY fk_name1;  
ALTER TABLE table2 DROP FOREIGN KEY fk_name2;

Then recreate foreign key constraints along with ON DELETE SET NULL condition, as shown below.

ALTER TABLE table1 
  ADD FOREIGN KEY (table2_id) 
        REFERENCES table2(id)
        ON DELETE SET NULL;

ALTER TABLE table2 
  ADD FOREIGN KEY (table1_id) 
        REFERENCES table1(id)
        ON DELETE SET NULL;

Now when you delete records from either tables, the foreign keys present in the other table will be automatically set to NULL and you won’t get any error messages.

Foreign Key constraints are useful and important to maintain data integrity in MySQL database. But sometimes you may need to delete rows/column values without affecting related tables in your database. You can use the above mentioned commands to temporaily disable foreign key constraint.

Please remember to add back the foreign key constraint after you delete the required data from your table. Otherwise, it can cause data sanity issues for you later on.

Also read:

How to Lookup Dictionary by Key in Django Template
How to Run Python Script in Django Shell
How to Convert PDF to Image/JPG in Linux
How to Check if Cookie is Set in Apache Server
How to Parse JSON in NodeJS