How to disable foreign key constraint in MySQL

In this article, we will see how to disable foreign key constraints temporarily in MySQL.

It is not a good idea to disable the foreign key constraint to remove the errors that occur. Foreign keys are there to maintain data integrity. Still, in many cases, we need to disable the foreign key constraint, for example, while transferring some data or while making database updates. In all these situations, one can temporarily disable the constraints and set them back in later.

SCRIPT FOR DISABLING:-

We can disable MySQL foreign key constraints by executing:

SET FOREIGN_KEY_CHECKS=0;

SCRIPT FOR ENABLING:-

We can enable MySQL foreign key constraints by executing:

SET FOREIGN_KEY_CHECKS=1;

To Disable and Enable Global Foreign Key Constraints:-

SET GLOBAL FOREIGN_KEY_CHECKS = 0;
SET GLOBAL FOREIGN_KEY_CHECKS = 1;

EXAMPLE FOR DEMONSTRATION:-

Let us view how to do the same. We will be preparing the sample data by creating two tables, employee_details, and employee_rating, with a foreign key relationship between them followed by inserting data to them.

#create the table employee_details
CREATE TABLE employee_details(
  emp_id int  NOT NULL AUTO_INCREMENT,
  emp_name varchar(255) DEFAULT NULL,
  emp_designation varchar(255) DEFAULT NULL,
  primary key(emp_id)
);
#insert data to employee_details
INSERT INTO employee_details (emp_id,emp_name,emp_designation) 
 VALUES(1,"Henry","Assistant Manager"),
 (2,"Richa","Executive"),
 (3,"Veronica","Executive"),
 (4,"George","Executive"),
 (5,"Harsh","Assistant Manager");
#create the table employee_rating
CREATE TABLE employee_rating(
   id int AUTO_INCREMENT,
   emp_id int,
   emp_rating int,
   primary key(id),
   foreign key (emp_id) references employee_details(emp_id)
);
#insert data to employee_rating
INSERT INTO employee_rating (emp_id,emp_rating) 
 VALUES(1,1),
 (2,3),
 (3,4),
 (4,1),
 (5,2);

Here, emp_id in the table employee_rating has a foreign key relationship with emp_id in the employee_details table. To view the snapshot of both the tables, execute:

SELECT * FROM employee_details;

Output:-

SELECT * FROM employee_rating;

Output:-

If we try to delete the data from the employee_details table, we will get the below error because of the foreign key constraint.

DELETE FROM employee_details WHERE emp_id=4;

Action Output:-

Error Code: 1451. Cannot delete or update a parent row:a foreign key constraint fails (thispointerdb.employee_rating, CONSTRAINT employee_rating_ibfk_1 FOREIGN KEY (emp_id) REFERENCES employee_details (emp_id))

Let us now execute the below query to disable foreign key constraints:-

 SET FOREIGN_KEY_CHECKS=0;

Action Output:-

Re-run the same delete statement.

DELETE FROM employee_details WHERE emp_id=4;

Action Output:-

Delete is successful this time. Hence the check worked.

Let us now enable the foreign key constraints back by executing:

 SET FOREIGN_KEY_CHECKS=1;

Action Output:-

Re-running the delete query will again throw an error.

DELETE FROM employee_details WHERE emp_id=3;

Action Output:-

READ MORE:

• Find all tables with specific column names in MySQL
• MySQL add primary key multiple columns
• Mysql update set multiple columns
• Mysql update column with value from another table
• Select all columns except one of a MySQL table
• MySQL ADD COLUMN IF NOT EXISTS

We hope this article helped you with enabling and disabling the foreign key constraints in MySQL. Good Luck!!!