MySQL Disable Foreign Key Checks

In this tutorial, we will learn how to disable foreign key checks in MySQL.

MySQL’s Foreign Key Check feature can stop you from updating some databases’ tables with foreign key limitations. In certain situations, you can make your adjustments while MySQL’s foreign key check is temporarily disabled, and then re-enable it. It can be difficult to load data into the parent tables first, followed by the child tables in order, if you don’t disable foreign key checks.

To disable the foreign key check, use the following-

SET foreign_key_checks = 0;

To again enable the foreign key check, use the following-

SET foreign_key_checks = 1;

MySQL won’t re-validate the data you added after disabling foreign key checks after you enable foreign key checks. It will only examine any recent updates or additions to your database.

Example to Disable Foreign Key checks

First of all, create a table named State.

Create Table State(
id INT AUTO_INCREMENT,
name VARCHAR(300) NOT NULL,
PRIMARY KEY(id)
);Code language: PHP (php)

Next, create another table named City.

Create Table City(
c_id INT AUTO_INCREMENT,
c_name VARCHAR(300) NOT NULL,
id INT,
PRIMARY KEY(c_id),
FOREIGN KEY(id)
REFERENCES State(id)
);Code language: PHP (php)

Now insert value in the table City.

INSERT INTO City(c_name, id)
VALUES('Bhopal', 1);Code language: JavaScript (javascript)

MySQL will give this error-

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`id`) REFERENCES `state` (`id`))Code language: JavaScript (javascript)

For this disable foreign key checks-

SET foreign_key_checks = 0;

Now again insert values into the City table and check it.

INSERT INTO City(c_name, id)
VALUES('Bhopal', 1);
SELECT * FROM City;Code language: JavaScript (javascript)

Output-

Lastly, enable the foreign key checks.

SET foreign_key_checks = 1;

And now insert values into the State table.

INSERT INTO State(name, id)
VALUES('M.P.', 1);
SELECT * FROM State;Code language: JavaScript (javascript)

Output-

Example of Dropping tables with Foreign key constraints

Let us assume that we want to drop the table State and City.

Drop Table State;

It gives us this error-

ERROR 3730 (HY000): Cannot drop table ‘state’ referenced by a foreign key constraint ‘city_ibfk_1’ on table ‘city’.

Disable the Foreign key checks-

SET foreign_key_checks = 0;

Now drop both the tables-

Drop Table State;
Drop Table City;

Lastly, enable the foreign key checks-

SET foreign_key_checks = 1;

Conclusion

In this tutorial, we learned how to Disable Foreign Key checks in MySQL. For more reference, check out the official documentation of MySQL.