FOREIGN KEY constraint, like other database constraints, ensures the accuracy and reliability of the data. It prevents deleting data from the referenced table, which can cause bugs and inaccuracy.
But sometimes we need to disable the foreign key constraint temporarily or permanently, for example, when truncating a table or deleting column values.
How can you disable a foreign key constraint in MySQL?
There are multiple ways to disable the foreign key constraint in MySQL.
If you want to disable the foreign key constraint for specific tables, you can use the
DISABLE_KEYS statement. Run the following command after replacing the
table_name with your table name:
ALTER TABLE table_name DISABLE KEYS;
Once you have performed the required operations, you can re-enable foreign key checks by running the following command:
ALTER TABLE table_name ENABLE KEYS;
If you want to disable foreign key checks across all databases and tables, you can use the
FOREIGN_KEY_CHECKS statement. In your MySQL execute the following command:
You can re-enable the constraint using the following command:
You may want to change the behavior of the foreign key permanently. For example, you may require that when you delete a record, the foreign key in the dependent tables should be set to
To do this, first drop the existing foreign key constraint in the dependent table, like so:
ALTER TABLE table_name DROP FOREIGN KEY fk_name1;
And then recreate the constraint with
ON DELETE SET NULL condition:
ALTER TABLE table_name ADD FOREIGN KEY (other_table_id) REFERENCES other_table(id) ON DELETE SET NULL;