JIYIK CN >

In this article we will learn how to use FOREIGN_KEY_CHECKS in MySQL Workbench to temporarily turn off foreign key constraints in MySQL.

Disable foreign key constraints in MySQL

There are many situations where we temporarily turn off foreign keys. For example, loading data into parent and child tables in an arbitrary order.

In this case, we can use FOREIGN_KEY_CHECKS to turn off foreign key constraints in MySQL Server. To understand this, let's first create two tables and populate them.

Sample code:

# create a `student` table
CREATE TABLE student(
    student_id INT NOT NULL PRIMARY KEY,
    student_name VARCHAR(255) NOT NULL
);

# create a `course` table
CREATE TABLE course(
    course_id INT NOT NULL PRIMARY KEY,
    course_name VARCHAR(255),
    student_id INT,
    FOREIGN KEY(student_id)
    REFERENCES student(student_id)
);

Example code:

# insert data into the `student` table
INSERT INTO student(student_id, student_name)
VALUES
(1, 'Maryam Taymor'),
(2, 'Mehvish Ashiq'),
(3, 'James Daniel'),
(4, 'Rahul Agarwal');

# insert data into the `course` table
INSERT INTO course(course_id, course_name, student_id)
VALUES
(1, 'Java Programming', 4),
(2, 'Data Science', 3),
(3, 'Computer Vision', 2),
(4, 'Python Programming', 1);
Use the SELECT command to see the current data in both tables.

SELECT * FROM student;
SELECT * FROM course;

Output (student table):

+------------+---------------+
| student_id | student_name  |
+------------+---------------+
|          1 | Maryam Taymor |
|          2 | Mehvish Ashiq |
|          3 | James Daniel  |
|          4 | Rahul Agarwal |
+------------+---------------+
4 rows in set (0.00 sec)

Output (course table):

+-----------+----------------------+------------+
| course_id | course_name          | student_id |
+-----------+----------------------+------------+
|         1 | Java Programming     |          4 |
|         2 | Data Science         |          3 |
|         3 | Computer Vision      |          2 |
|         4 | Python Programming   |          1 |
+-----------+----------------------+------------+
4 rows in set (0.00 sec)

Set FOREIGN_KEY_CHECKS to turn off foreign keys in MySQL

Suppose we have another student who wants to enroll in Java Programming. This time, we need to insert the record into the child table (Courses table) first.

Sample code:

INSERT INTO course(course_id,course_name, student_id)
VALUES
(5, 'Java Programming', 5);

As soon as we execute the query to insert a record in the courses table, it generates the following error.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ms23`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`))

This happens because there is a foreign key in the Courses table. Therefore, we can disable the foreign key check as follows.

SET foreign_key_checks = 0;

Now, insert it again into the course table.

INSERT INTO course(course_id,course_name, student_id)
VALUES
(5, 'Java Programming', 5);

This time, the record is inserted successfully. Confirm the insertion using the SELECT command.

SELECT * from course;

Output (course table):

+-----------+----------------------+------------+
| course_id | course_name          | student_id |
+-----------+----------------------+------------+
|         1 | Java Programming     |          4 |
|         2 | Data Science         |          3 |
|         3 | Computer Vision      |          2 |
|         4 | Python Programming   |          1 |
|         5 | Java Programming     |          5 |
+-----------+----------------------+------------+
5 rows in set (0.00 sec)

Don't forget to set the value of FOREIGN_KEY_CHECKS to 1 to re-enable foreign key constraint checking.

SET foreign_key_checks = 1;

We can also use the same command in phpMyAdmin, but make sure to uncheck the options highlighted in the following screenshot.

Disable foreign key constraints in mysql - Disable foreign key in phpmyadmin

Remember that when we FOREIGN_KEY_CHECKSset the value of back to 1, it will not trigger any validation on the current data we inserted after turning off foreign key checking.

It only checks the database for new updates and additions. We can also disable foreign key constraints globally using FOREIGN_KEY_CHECKS.

Sample code:

# turn off foreign key constraints globally
SET GLOBAL FOREIGN_KEY_CHECKS=0;
# turn on foreign key constraints globally
SET GLOBAL FOREIGN_KEY_CHECKS=1;