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:-

image_1
SELECT * FROM employee_rating;

Output:-

image_2

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:-

image_3

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:-

image_3

Re-run the same delete statement.

DELETE FROM employee_details WHERE emp_id=4;

Action Output:-

image_4

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:-

image_5

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

DELETE FROM employee_details WHERE emp_id=3;

Action Output:-

image_6

READ MORE:

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