How to Fix MySQL Error 1452?

Looking for a solution to MySQL Error 1452? You can rely on us.

As a part of our Server Management Services, we at Skynats provide answers for all kinds of queries, both big and small.

Let’s look at how our support staff can assist clients who are experiencing MySQL Error 1452.

How do I fix MySQL Error 1452?

This error typically happens when we attempt to run a data manipulation query into a table that contains one or more broken foreign key constraints.

Why does MySQL Error 1452 occur?

The values we are attempting to insert into the table are not present in the referencing (parent) table, which is the root cause of this error.

A Foreign Key is a table column that is referenced from another table.

Consider the table City, which contains the name and ID of a city.

Additionally, there are table Buddies for keeping track of people we know who reside in different cities.

As stated below, the id column of the City table must be used as the FOREIGN KEY of the city_id column in the friends’ table:

CREATE TABLE friends (
firstName varchar(255) NOT NULL,
city_id int unsigned NOT NULL,
PRIMARY KEY (firstName),
CONSTRAINT friends_ibfk_1
FOREIGN KEY (city_id) REFERENCES City (id)
)

In the preceding code, a CONSTRAINT named buddies_ibfk_1 is created for the city_id column, which refers to the id column of the City table.

This CONSTRAINT states that the city id column can only accept values from the id column.

The following error will appear if we attempt to insert a value into the city_id column that does not already exist in the id column:

ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
(test_db.friends, CONSTRAINT friends_ibfk_1
FOREIGN KEY (city_id) REFERENCES city (id))

How do you fix it?

Let’s look at what our support technicians did to fix it:

The MySQL database server ERROR 1452 can be fixed in one of two ways:

1. First, enter the value in the associated table.
2. Afterward, turn off FOREIGN_KEY_CHECKS on the server

1. Include the value in the table that is mentioned.

The first choice is to add the necessary value to the table that is being referenced.

In the preceding example, add the necessary id value to the City table.

The city_id value we previously inserted can now be used to add a new row to the Buddies table.

Disabling the foreign key check

2. In the MySQL server, disable the FOREIGN_KEY_CHECKS variable.

Run the following command to see if the variable is active or not:

SHOW GLOBAL VARIABLES LIKE ‘FOREIGN_KEY_CHECKS’;

 — +——————–+——-+
— | Variable_name | Value |
— +——————–+——-+
— | foreign_key_checks | ON |
— +——————–+——-+

Before inserting or updating, this variable instructs MySQL to verify any foreign key constraints that have been added to our table(s).

The variable can be disabled for the current session or globally:

— set for the current session:
SET FOREIGN_KEY_CHECKS=0;

— set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=0;

We can now INSERT or UPDATE rows in our table without fear of breaking a foreign key constraint.

When the manipulation query is finished, we can make the FOREIGN_KEY_CHECKS active once more by setting its value to 1:

— set for the current session:
SET FOREIGN_KEY_CHECKS=1;

— set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=1;

The city_id column will refer to a NULL column in the City table if the FOREIGN_KEY_CHECKS variable is disabled.

In the future, if we need to run a JOIN query, it might become problematic.

Are you looking for an answer to another query? Contact our technical support team.