MySQL Disable Foreign Key Constraint

The Foreign key constraint feature provides data integrity in any database. It prevents deleting data from the reference table or parent table.

But sometimes, we need to disable the Foreign key checks temporarily or permanently for performing particular tasks like truncating table, deleting column values, data migration, etc.

There are multiple ways to disable the Foreign key constraint in MySQL.

Disable Foreign Key Constraint for specific table

If you want to disable a foreign key constraint from a specific table, you can use the following syntax.

ALTER TABLE table_name DISABLE KEYS;

After completion of the specific work on the table, you can re-enable the Foreign key constraint by the following statement.

ALTER TABLE table_name ENABLE KEYS;

Disable Foreign Key Constraints for all databases and tables

In case, you want to disable Foreign key checks across all the databases and tables, you can use FOREIGN_KEY_CHECKS command.

Syntax:-

SET FOREIGN_KEY_CHECKS=0;

Once you are done with the work, do not forget to enable the Foreign key constraint. Otherwise, for any data inserted after the disabling of the Foreign key check, MySQL will not check any data related to the Foreign key check which may lead to inconsistency in data. To re-enable the constraint, you can use the following statement.

SET FOREIGN_KEY_CHECKS=1;

Disable Foreign Key Constraints Example

Now let’s understand the concept of disabling foreign key concepts with a few examples.

Setting up the environment:-

Let’s create two tables Students and Courses.

Table: Students

CREATE TABLE STUDENTS (
	studentID INT PRIMARY KEY,
	firstName varchar(50),
	lastName varchar(50),
	gender char(1),
	city varchar(20)
);

Table: Courses

CREATE TABLE COURSES (
	courseID INT PRIMARY KEY,
	courseName varchar(20),
	studentID INT,
	gender char(1),
	FOREIGN KEY (studentID) REFERENCES STUDENTS(studentID)
);

Now insert some values in these tables.

INSERT INTO STUDENTS(studentID,firstName,lastName,gender,city) VALUES
(1,'PRITAM','CHOUDHURY','M','PATNA'),
(2,'DEEPAK','KUMAR','M','KOLKATA'),
(3,'RAJU','BISWAS','M','DELHI'),
(4,'RIYA','DESAI','F','MUMBAI');

INSERT INTO COURSES(courseID,courseName,studentID) VALUES
(1,'JAVA',1),
(2,'ALGORITHM',1),
(3,'MACHINE LEARNING',2),
(4,'NETWORKING',3);

STUDENTS table:-

mysql> SELECT * FROM STUDENTS;
+-----------+-----------+-----------+--------+---------+
| studentID | firstName | lastName  | gender | city    |
+-----------+-----------+-----------+--------+---------+
|         1 | PRITAM    | CHOUDHURY | M      | PATNA   |
|         2 | DEEPAK    | KUMAR     | M      | KOLKATA |
|         3 | RAJU      | BISWAS    | M      | DELHI   |
|         4 | RIYA      | DESAI     | F      | MUMBAI  |
+-----------+-----------+-----------+--------+---------+
4 rows in set (0.00 sec)

Courses table:-

mysql> SELECT * FROM COURSES;
+----------+------------------+-----------+
| courseID | courseName       | studentID |
+----------+------------------+-----------+
|        1 | JAVA             |         1 |
|        2 | ALGORITHM        |         1 |
|        3 | MACHINE LEARNING |         2 |
|        4 | NETWORKING       |         3 |
+----------+------------------+-----------+
4 rows in set (0.00 sec)

Explanation: Here we have created two tables named Students and Courses where studentID is the foreign key which is referring studentID of the Students table.

By default, the foreign key constraint is enabled. Hence, the database will remain consistent even if we try to insert some inconsistent data into the table.

For example, in the Courses table if we try to insert the below rows let’s check whether data is getting inserted.

INSERT INTO COURSES(courseID,courseName,studentID) VALUES
(5,'JAVA',5);

Output:-

mysql> INSERT INTO COURSES(courseID,courseName,studentID) VALUES(5,'JAVA',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mysqlfk`.`courses`, CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`studentID`) REFERENCES `students` (`studentID`))

In the above example, we have seen that MySQL throws an error due to the foreign key constraint active in the Courses table as the value of the studentID=5 is not present in the parent table Students.

Now, let’s disable the foreign key constraint as below.

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

Now, let’s try again to insert the previous row and check if the row gets inserted.

mysql> INSERT INTO COURSES(courseID,courseName,studentID) VALUES(5,'JAVA',5);
Query OK, 1 row affected (0.94 sec)

After insertion of the above row, the table Courses will look like as below.

mysql> SELECT * FROM COURSES;
+----------+------------------+-----------+
| courseID | courseName       | studentID |
+----------+------------------+-----------+
|        1 | JAVA             |         1 |
|        2 | ALGORITHM        |         1 |
|        3 | MACHINE LEARNING |         2 |
|        4 | NETWORKING       |         3 |
|        5 | JAVA             |         5 |
+----------+------------------+-----------+

As you can see the row gets inserted into the Courses table, although the studentID=5 is not present in the parent table Students. This makes the database inconsistent.

To prevent this inconsistency, you can reenable the foreign key check by the following syntax.

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.03 sec)