Cannot add or update a child row a foreign key constraint fails как исправить

I have created tables in MySQL Workbench as shown below :

ORDRE table:

CREATE TABLE Ordre (
  OrdreID   INT NOT NULL,
  OrdreDato DATE DEFAULT NULL,
  KundeID   INT  DEFAULT NULL,
  CONSTRAINT Ordre_pk PRIMARY KEY (OrdreID),
  CONSTRAINT Ordre_fk FOREIGN KEY (KundeID) REFERENCES Kunde (KundeID)
)
  ENGINE = InnoDB;

PRODUKT table:

CREATE TABLE Produkt (
  ProduktID          INT NOT NULL,
  ProduktBeskrivelse VARCHAR(100) DEFAULT NULL,
  ProduktFarge       VARCHAR(20)  DEFAULT NULL,
  Enhetpris          INT          DEFAULT NULL,
  CONSTRAINT Produkt_pk PRIMARY KEY (ProduktID)
)
  ENGINE = InnoDB;

and ORDRELINJE table:

CREATE TABLE Ordrelinje (
  Ordre         INT NOT NULL,
  Produkt       INT NOT NULL,
  AntallBestilt INT DEFAULT NULL,
  CONSTRAINT Ordrelinje_pk PRIMARY KEY (Ordre, Produkt),
  CONSTRAINT Ordrelinje_fk FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID),
  CONSTRAINT Ordrelinje_fk1 FOREIGN KEY (Produkt) REFERENCES Produkt (ProduktID)
)
  ENGINE = InnoDB;

so when I try to insert values into ORDRELINJE table i get:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (srdjank.Ordrelinje, CONSTRAINT Ordrelinje_fk FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID))

I’ve seen the other posts on this topic, but no luck.
Am I overseeing something or any idea what to do?

Wondering how to resolve MySQL Error 1452? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Microsoft SQL Server Support Services.

Let’s take a look at how our Support Team is ready to help customers with MySQL Error 1452.

How to resolve MySQL Error 1452?

Usually, this error occurs when we try to execute a data manipulation query into a table that has one or more failing foreign key constraints.

What causes MySQL Error 1452?

The cause of this error is the values we are trying to put into the table are not available in the referencing (parent) table.

When a column of a table is referenced from another table, it is called Foreign Key.

For example, consider a table City that contains the name of a city and its ID.

Also, there is another table Buddies to keep a record of people that we know who lives in different cities.

We have to reference the id column of the City table as the FOREIGN KEY of the city_id column in the friends table as follows:

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 code above, a CONSTRAINT named buddies_ibfk_1 is created for the city_id column, referencing the id column in the City table.

This CONSTRAINT means that only values in the id column can be inserted into the city_id column.

If we try to insert a value that is not present in id column into the city_id column, it will trigger the error as shown below:

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 to resolve it?

Today, let us see the steps followed by our Support Techs to resolve it:

There are two ways to fix the ERROR 1452 in MySQL database server:

1. Firstly, add the value into the referenced table
2. Then, disable the FOREIGN_KEY_CHECKS in the server

1. Add the value into the referenced table

The first option is to add the value we need to the referenced table.

In the example above, add the required id value to the City table.

Now we can insert a new row in the Buddies table with the city_id value that we inserted.

Disabling the foreign key check

2. Disable the FOREIGN_KEY_CHECKS variable in MySQL server.

We can check whether the variable is active or not by running the following query:

SHOW GLOBAL VARIABLES LIKE ‘FOREIGN_KEY_CHECKS’;

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

This variable causes MySQL to check any foreign key constraint added to our table(s) before inserting or updating.

We can disable the variable for the current session only or globally:

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

Now we can INSERT or UPDATE rows in our table without triggering a foreign key constraint fails.

After we are done with the manipulation query, we can set the FOREIGN_KEY_CHECKS active again by setting its value to 1:

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

Turning off FOREIGN_KEY_CHECKS variable will cause the city_id column to reference a NULL column in the City table.

It may cause problems when we need to perform a JOIN query later.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to resolve MySQL Error 1452.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

The MySQL ERROR 1452 happens when you try to execute a data manipulation query into a table that has one or more failing foreign key constraints.

The cause of this error is the values you’re trying to put into the table are not available in the referencing (parent) table.

Let’s see an example of this error with two MySQL tables.

Suppose you have a Cities table that contains the following data:

+----+------------+
| id | city_name  |
+----+------------+
|  1 | York       |
|  2 | Manchester |
|  3 | London     |
|  4 | Edinburgh  |
+----+------------+

Then, you create a Friends table to keep a record of people you know who lives in different cities.

You reference the id column of the Cities table as the FOREIGN KEY of the city_id column in the Friends table as follows:

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 `Cities` (`id`)
)

In the code above, a CONSTRAINT named friends_ibfk_1 is created for the city_id column, referencing the id column in the Cities table.

This CONSTRAINT means that only values recoded in the id column can be inserted into the city_id column.

(To avoid confusion, I have omitted the id column from the Friends table. In real life, You may have an id column in both tables, but a FOREIGN KEY constraint will always refer to a different table.)

When I try to insert 5 as the value of the city_id column, I will trigger the error as shown below:

INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 5);

The response from MySQL:

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 `cities` (`id`))

As you can see, the error above even describes which constraint you are failing from the table.

Based on the Cities table data above, I can only insert numbers between 1 to 4 for the city_id column to make a valid INSERT statement.

INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 1);

-- Query OK, 1 row affected (0.00 sec)

The same error will happen when I try to update the Friends row with a city_id value that’s not available.

Take a look at the following example:

UPDATE `Friends` SET city_id = 5 WHERE `firstName` = 'John';

-- ERROR 1452 (23000): Cannot add or update a child row

There are two ways you can fix the ERROR 1452 in your MySQL database server:

  • You add the value into the referenced table
  • You disable the FOREIGN_KEY_CHECKS in your server

The first option is to add the value you need to the referenced table.

In the example above, I need to add the id value of 5 to the Cities table:

INSERT INTO `Cities` VALUES (5, 'Liverpool');

-- Cities table:
+----+------------+
| id | city_name  |
+----+------------+
|  1 | York       |
|  2 | Manchester |
|  3 | London     |
|  4 | Edinburgh  |
|  5 | Liverpool  |
+----+------------+

Now I can insert a new row in the Friends table with the city_id value of 5:

INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Susan', 5);

-- Query OK, 1 row affected (0.00 sec)

Disabling the foreign key check

The second way you can fix the ERROR 1452 issue is to disable the FOREIGN_KEY_CHECKS variable in your MySQL server.

You can check whether the variable is active or not by running the following query:

SHOW GLOBAL VARIABLES LIKE 'FOREIGN_KEY_CHECKS';

-- +--------------------+-------+
-- | Variable_name      | Value |
-- +--------------------+-------+
-- | foreign_key_checks | ON    |
-- +--------------------+-------+

This variable causes MySQL to check any foreign key constraint added to your table(s) before inserting or updating.

You can disable the variable for the current session only or globally:

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

-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=0;

Now you can INSERT or UPDATE rows in your table without triggering a foreign key constraint fails:

INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Natalia', 8);
-- Query OK, 1 row affected (0.01 sec)

UPDATE `Friends` SET city_id = 17 WHERE `firstName` = 'John';
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

After you’re done with the manipulation query, you can set the FOREIGN_KEY_CHECKS active again by setting its value to 1:

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

-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=1;

But please be warned that turning off your FOREIGN_KEY_CHECKS variable will cause the city_id column to reference a NULL column in the cities table.

It may cause problems when you need to perform a JOIN query later.

Now you’ve learned the cause of ERROR 1452 and how to resolve this issue in your MySQL database server. Great work! 👍

Удалил все магазины, вообщем связь получилась. Но что бы я не выставлял в ON UPDATE и ON DELETE, все равно при добавлении в таблицу add_photos_store данных через админку, такая ошибка «Cannot add or update a child row: a foreign key constraint fails (`freeman_domolux`.`add_photos_store`, CONSTRAINT `add_photos_store_ibfk_1` FOREIGN KEY (`magazin_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)» это я перевожу «Невозможно добавить или обновить дочернюю строку: ограничение внешнего ключа не выполняется» ограничения я не ставил, почему такая ошибка? Но зато через phpmyadmin инфу можно вставить в add_photos_store и там я выбираю при вставке id магазина, все отображается но все одинаковое в каждом магазине, это из за выборки. Нужно писать выборку так select * from add_photos_store where magazin_id = n, где n — id магазина т.е magazin_id = id но вылазит такая ошибка «Unknown column ‘magazin_id’ in ‘where clause'». Что не так делаю?

The cannot add or update a child row: a foreign key constraint fails bug appears when a table has one or more incorrect foreign key constraints. Developers refer to it as a MySQL error because it affects the functions and messes up your user experience in the database server.cannot add or update a child row

Therefore, programmers must change values inside the integrity constraint violation to debug the error and make the code functional. As a result, we wrote this in-depth guide explaining the inability to add or update a row: a key constraint fails error code and the possible solutions, so keep reading for more.

Contents

  • Why Is the Cannot Add or Update a Child Row: A Foreign Key Constraint Fails Error Happening?
    • – Creating Two MySQL Tables
    • – Inserting Values Inside the Columns
  • How To Fix the Cannot Add or Update a Child Row: A Foreign Key Constraint Fails Error?
    • 1. Adding the Value in the Referenced Table
    • 2. Disable the Foreign Key Control
  • Conclusion

Why Is the Cannot Add or Update a Child Row: A Foreign Key Constraint Fails Error Happening?

The cannot add or update a child row: a foreign key constraint fails spring boot error happens due to the incorrect executions inside the data manipulation query. As a result, the code affects the child table with several failing key constraints and displays the error.

The cannot add or update a child row: a foreign key constraint fails hibernate error can also have severe consequences on your child table’s visual appearance. Although the error may discourage and frustrate novice programmers and developers, we urge you not to worry because several error code solutions exist.

For instance, developers sometimes include values into a table unavailable in the parent element, creating an integrity constraint violation.

Furthermore, the key fails and displays the error code because it cannot locate the values requested by the child row. As a result, developers must change the values and tags in the adequate table and child row, which does not require much effort and solves the integrity constraint violation.

The cannot add or update a child row: a foreign key constraint fails typeorm error will no longer appear and affect your programming experience. However, we must recreate the error using two MySQL tables to comprehend how the key constraint fails.

– Creating Two MySQL Tables

The first culprit for this integrity constraint violation is creating two MySQL tables causing multiple key constraint fails. The initial step to creating a MySQL table is to include an id with several options.Cannot Add or Update a Child Row A Foreign Key Constraint Fails Causes

This example has a container labeled “Cities” with four options:

+—-+————+

| id | city_name |

+—-+————+

| 1 | New York |

| 2 | Tokyo |

| 3 | London |

| 4 | Paris |

+—-+————+

In addition, developers must create another table with a different label; in this case, we will label it “People”. However, this is not the complete code that causes the cannot add or update a child row: a foreign key constraint fails sequelize error because you must reference the columns.

The id reference triggers the integrity constraint violation, so let us learn what happens:

CREATE TABLE `People` (

`firstName` varchar(275) NOT NULL,

`city_id` int unsigned NOT NULL,

PRIMARY KEY (`firstName`),

CONSTRAINT `people_ibfk_1`

FOREIGN KEY (`city_id`) REFERENCES `Cities` (`id`)

)

As you can see, we created an integrity constraint violation named `people_ibfk_1` for the city column, referencing the id tab inside the Cities parent table. Still, the key fails and affects the primary function.

– Inserting Values Inside the Columns

After completing the two MySQL tables, users should insert values inside the columns. However, this activates the integrity constraint violation because MySQL does not register the matter correctly. As a result, the key constraint fails, and developers receive a response from MySQL.

Customers can insert a value of 6, as shown in this example:

INSERT INTO `People` (`firstName`, `city_id`) VALUES (‘Bill’, 6);

The response from MySQL will appear immediately and prevent users from entering the appropriate values, as shown in this example:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails PHPmyadmin

(`test_db`.`people `, CONSTRAINT `people_ibfk_1`

FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`))

It is impossible to complete this straightforward task due to the integrity constraint violation that affects both MySQL tables. However, the first table states that users cannot include higher values than four, so MySQL renders the number six as an incorrect value.

This example shows how to create a correct integrity constraint violation without errors:

INSERT INTO `Friends` (`firstName`, `city_id`) VALUES (‘Bill’, 2);

— Query OK, 1 row affected (0.00 sec)

Although this seems and looks logical, many professional developers are frustrated when the foreign key constraint fails and ruins the user experience. However, it would be best if you did not worry because the following chapters provide a few solutions that will remove the annoying key error from your document.

The easiest method of debugging this integrity constraint violation from your program is by adding the correct value to the required table. As a result, both MySQL tables will have appropriate elements and values.



However, this is not the only solution because developers can disable their server’s foreign critical checks. Furthermore, they will not ruin the user experience by displaying the annoying cannot add or update a child row: a foreign key constraint fails syntax.

Therefore, the server will not check the key values, preventing the error from appearing. On the flip side, the second solution can affect the whole document, so programmers must be aware of its consequences.

Still, our experts teach you how to debug the cannot add or update a child row: a foreign key constraint code using both methods.

1. Adding the Value in the Referenced Table

The first method requires developers to include extra integrity constraints in the parent table to prevent constant key constraint fails. However, they must use the exact value that creates the error, which can be higher or lower than the values already in the table.Cannot Add or Update a Child Row A Foreign Key Constraint Fails Fixes

This table shows the same cities from before with a few additions:

INSERT INTO `Cities` VALUES (5, 6, Kalkuta, Moscow, ‘);

— Cities table:

+—-+————+

| id | city_name |

+—-+————+

| 1 | New York |

| 2 | Tokyo |

| 3 | London |

| 4 | Paris |

| 5 | Kalkuta |

| 5 | Moscow |

+—-+————+

The options inside the table match the number six from the user interface, preventing foreign key constraints from failing. As a result, customers can insert the following code without messing up the document:

INSERT INTO `People` (`firstName`, `city_id`) VALUES (‘Mike’, 6);

— Query OK, 1 row affected (0.00 sec)

This fully-proof method always works, but let us see how you can disable the key control.

2. Disable the Foreign Key Control

The second method requires developers to disable the key control, but first, they must check if it is on or off. Learn how to check the key control activity in the following example:

SHOW GLOBAL VARIABLES LIKE ‘FOREIGN_KEY_CONTROLS’;

— +——————–+——-+

— | Variable_name | Value |

— +——————–+——-+

— | foreign_key_checks | ON |

— +——————–+——-+

As you can see, the value shows the key control feature is on, causing the typical key constraint fails inside your MySQL tables. However, disabling the control is easy and requires several lines of code, which are not challenging to replicate.

Disabling the current session or globally is possible, as shown in this example:

— set for the current session:

SET FOREIGN_KEY_CONTROLS =0;

— set globally:

SET GLOBAL FOREIGN_KEY_CONTROLS =0;

The key control error will no longer appear because the system will not check the inputs. However, our experts recommend using the previous solution and disabling the key control checks.

Conclusion

The cannot add or update a child row: a foreign key constraint fails is a standard error caused by incorrect values inside two MySQL tables. Although we helped you understand the bug’s most critical aspects and facts, remembering the details in the following bullet list would be great:

  • This error messes up the user experience and interferes with other MySQL functions and operations
  • The foreign key constraint error will not appear if you create a single MySQL table
  • Two possible solutions to this error exist, but we recommend using the first one
  • Disabling the key control checks might affect other elements and values
  • The solution codes are easy to remember and replicate in your syntax

Our complete guide provides the necessary steps to fix the key constraint fails in your document. Therefore, you no longer have to search for the best solution to this annoying error.

  • Author
  • Recent Posts

Position is Everything

Your Go-To Resource for Learn & Build: CSS,JavaScript,HTML,PHP,C++ and MYSQL. Meet The Team

Position is Everything

Понравилась статья? Поделить с друзьями:
  • Как найти силу если известна масса длина
  • Как найти ао нпф достойное будущее
  • Как можно найти мой айфон
  • Как найти время если известен путь
  • Как найти одинаковые значения на разных листах