How to Turn Off Foreign Key Constraint in MySQL
- Turn Off Foreign Key Constraint in MySQL
-
Set the
FOREIGN_KEY_CHECKSto Turn Off the Foreign Key in MySQL
Today, we will learn to use FOREIGN_KEY_CHECKS in MySQL Workbench to temporarily turn off foreign key constraints in MySQL.
Turn Off Foreign Key Constraint in MySQL
There are various situations when we temporarily turn off the foreign keys. For instance, loading data into the parent and child table in any order.
In that case, we can use the FOREIGN_KEY_CHECKS to turn off foreign key constraints in MySQL Server. To learn that, let’s create two tables and populate them first.
Example 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 (for 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 (for 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 the FOREIGN_KEY_CHECKS to Turn Off the Foreign Key in MySQL
Let’s say we have another student that wants to register in Java Programming. This time, we want to insert that record in the child table (course table) first.
Example Code:
INSERT INTO course(course_id,course_name, student_id)
VALUES
(5, 'Java Programming', 5);
As soon as we execute the query to insert the record in the course 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`))
It happens due to having a foreign key in the course table. So, we can disable the foreign key checks 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. Use the SELECT command to confirm the insertion.
SELECT * from course;
Output (for 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)
Do not forget to set the FOREIGN_KEY_CHECKS’s value to 1 to re-enable the foreign key constraint check.
SET foreign_key_checks = 1;
We can also use the same commands in phpMyAdmin, but make sure to UNCHECK the option highlighted in the following screenshot.
Remember, when we set the value of FOREIGN_KEY_CHECKS back to 1, it does not trigger any validation on the current data that we inserted after turning off the foreign key checks.
It only checks the new updates and additions to the database. We can also use the FOREIGN_KEY_CHECKS to disable foreign key constraints globally.
Example 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;
