How to Disable Foreign Key Check in MySQL

Sreeram Sreenivasan

Foreign Key Check in MySQL can prevent you from making some database updates to tables that have foreign key constraints. In such cases, you can temporarily disable foreign key check in MySQL, make your updates and enable foreign key check in MySQL afterwards. Here’s how to disable foreign key constraint in MySQL.

 

How to Disable Foreign Key Check in MySQL

Here are the steps to disable foreign key check in MySQL.

You can disable foreign key check in MySQL by setting the system variable foreign_key_checks to 0.

SET foreign_key_checks = 0

Similarly, you can enable foreign key check in MySQL by setting the system variable foreign_key_check to 1

SET foreign_key_checks = 1

However, please note, after you enable foreign key checks, MySQL will not re-validate your existing data that you added after disabling foreign key check. It will only check any new additions/updates to your database.

Bonus Read : How to Create Sequence in MySQL

Let us look at an example. Let’s say you have the following two tables countries and cities such that cities has a foreign key column country_id that refers to country_id in countries

CREATE TABLE countries(
    country_id INT PRIMARY KEY AUTO_INCREMENT,
    country_name VARCHAR(255) NOT NULL
);

 

CREATE TABLE cities(
    city_id INT PRIMARY KEY AUTO_INCREMENT,
    city_name VARCHAR(255),
    country_id INT,
    FOREIGN KEY(country_id) 
		REFERENCES countries(country_id)
);

 

Bonus Read : How to Compare Two Tables in MySQL

 

Now let’s try inserting a row in cities table without mentioning the country_id foreign key value and you will get an error as shown below.

mysql> INSERT INTO cities(city_name, country_id)
       VALUES('NYC',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`sample`.`cities`, CONSTRAINT `cities_ibfk_1` FOREIGN KEY (`country_id`)
 REFERENCES `countries` (`country_id`))

Now let us disable foreign key check in MySQL and try inserting the row again.

mysql> SET foreign_key_checks = 0;

mysql> INSERT INTO cities(city_name, country_id)
       VALUES('NYC',1);

mysql> select * from cities;
+---------+-----------+------------+
| city_id | city_name | country_id |
+---------+-----------+------------+
|       2 | NYC       |          1 |
+---------+-----------+------------+

Now we enable foreign check in MySQL

mysql> SET foreign_key_checks = 1;

Bonus Read : How to Get Last One Month Data in MySQL

We finally insert a corresponding row in countries table.

mysql> INSERT INTO countries(country_id, country_name)
       VALUES(1,'USA');

mysql> select * from countries;
+------------+--------------+
| country_id | country_name |
+------------+--------------+
|          1 | USA          |
+------------+--------------+

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Related posts:

How to Remove Not Null Constraint in MySQL
How to Get Records from Last 7 Days in MySQL
How to Compare Null Values in MySQL