How to Temporarily Disable a Foreign Key Constraint in MySQL?

MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995.

MySQL is reputed for its sturdy and quick functioning attributes which involve easy-to-handle features and dependability. MySQL can normally be seen together with dynamic web applications and is generally used to serve languages such as PHP but also other server-side programming languages like Python.

In this article, you will discover how we can temporarily disable a foreign key constraint in MySQL along with some examples.

Temporarily Disable a Foreign Key Constraint in MySQL

In MySQL, a foreign key is a relational constraint, which links two tables and uses a field (called referential integrity) to ensure information integrity. It establishes a correlation between the columns of a table called the referenced table or the parent table, and columns of another table called the child table or the referencing table.

They make sure that the lists of tables are always correct by requiring that the values on the foreign key columns of one table match the primary key columns of another table. Foreign key constraints act as a guard for maintaining data accuracy; there may be cases where we may need to disable them, e.g. during data migration or bulk data operations.

Syntax:

SET FOREIGN_KEY_CHECKS = 0;

Explanation: In the above syntax we are disabling the FOREIGN_KEY constraint by making it 0. This will apply to all the databases and tables. To enable the FOREIGN_KEY constraint again we will make it 1.

Example to Disable FOREIGN_KEY Constraint

Setting up environment

Let’s CREATE two tables for customers and orders

CREATE TABLE customers (
cust_id INT PRIMARY KEY,
 name VARCHAR(20),
 city VARCHAR(20));

CREATE TABLE orders(
order_id INT PRIMARY KEY, 
order_no INT,
cust_id INT, 
FOREIGN KEY (cust_id) REFERENCES customers(cust_id));

Insert values in it:

INSERT into customers (cust_id, name, city) values
(1, 'Sahil', 'Pune'),
(2, 'Sambhav', 'Kolhapur'),
(3, 'Athul', 'Aurangabad'),
(4, 'Anurag', 'Mumbai'),
(5, 'Roshan', 'Pune');

INSERT into orders(order_id, order_no, cust_id) values
(1, 101, 1),
(2, 102, 1),
(3, 103, 2),
(4, 104, 3);

customers Table:

Fig 1. customers Table

orders Table:

Fig 2. orders Table

Explanation: Here we are creating 2 tables for customers and orders. The customers table would have cust_id as its primary key, name, and city as columns and the orders table would have order_id as the primary key, order_no as another column, and cust_id as a foreign key.

If the FOREIGN KEY constraint is ENABLED then the database will remain consistent among the database even if we insert some inconsistent data.

In the above example, we will try to add a row in the orders table with order_id=5, order_no=105, and cust_id=6 and check whether it will get inserted or not.

Query:

INSERT into orders(order_id, order_no, cust_id) values
(5, 105, 6);

Output:

Fig 3. Foreign key constraint fails

Now Lets DISABLE the FOREIGN KEY Constraint

Syntax:

SET FOREIGN_KEY_CHECKS = 0;

Query:

SET FOREIGN_KEY_CHECKS = 0;

Output:

Fig 4. Disable Foreign key constraints

Now lets try to add a row in orders table with order_id=5, order_no=105, cust_id=6 and check whether it will get inserted or not.

Query:

INSERT into orders(order_id, order_no, cust_id) values
(5, 105, 6);

Output:

Fig 5. Inconsistent Table

Explanation: Here we are trying to add a row whose order_id=5, order_no=105, cust_id=6. As there is no row in the customers table whose cust_id=6 although there is a row in the the orders table whose cust_id=6. So there is a violation in the consistency of database.

Enabling FOREIGN KEY Constraint

We can also re enable the FOREIGN KEY constraint.

Syntax:

SET FOREIGN_KEY_CHECKS = 1;

Conclusion

Bringing a foreign key constraint to a halt in MySQL requires nullifying the (SET FOREIGN_KEY_CHECKS) command. By turning FOREIGN_KEY_CHECKS to 0 MySQL discontinues requiring validation of foreign keys, providing the ability to work with the data despite possible violations of foreign keys requirements.

Following the completion of all operations, the next step is to reset FOREIGN_KEY_CHECKS to 1, which will then enable foreign key constraints. This last step will restore the integrity of the data. Yet, it’s necessary to consider well the choice to erase the foreign key constraints and to tell the reasons for doing so because disabling this puts the data consistency and integrity in risky situation.