Ошибка 1452 phpmyadmin

I’m having a bit of a strange problem. I’m trying to add a foreign key to one table that references another, but it is failing for some reason. With my limited knowledge of MySQL, the only thing that could possibly be suspect is that there is a foreign key on a different table referencing the one I am trying to reference.

I’ve done a SHOW CREATE TABLE query on both tables, sourcecodes_tags is the table with the foreign key, sourcecodes is the referenced table.

CREATE TABLE `sourcecodes` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `language_id` int(11) unsigned NOT NULL,
 `category_id` int(11) unsigned NOT NULL,
 `title` varchar(40) CHARACTER SET utf8 NOT NULL,
 `description` text CHARACTER SET utf8 NOT NULL,
 `views` int(11) unsigned NOT NULL,
 `downloads` int(11) unsigned NOT NULL,
 `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `language_id` (`language_id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `sourcecodes_tags` (
 `sourcecode_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`sourcecode_id`),
 KEY `tag_id` (`tag_id`),
 CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This is the code that generates the error:

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

У меня немного странная проблема. Я пытаюсь добавить внешний ключ к одной таблице, которая ссылается на другую, но по какой-то причине она не работает. Имея мои ограниченные знания MySQL, единственное, что может быть подозрительным, это наличие внешнего ключа в другой таблице, ссылающейся на ту, которую я пытаюсь ссылаться.

Вот изображение моих связей в таблице, сгенерированное с помощью phpMyAdmin:
Отношения

Я выполнил запрос SHOW CREATE TABLE для обеих таблиц, sourcecodes_tags — это таблица с внешним ключом, sourcecodes — ссылочная таблица.

CREATE TABLE `sourcecodes` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `language_id` int(11) unsigned NOT NULL,
 `category_id` int(11) unsigned NOT NULL,
 `title` varchar(40) CHARACTER SET utf8 NOT NULL,
 `description` text CHARACTER SET utf8 NOT NULL,
 `views` int(11) unsigned NOT NULL,
 `downloads` int(11) unsigned NOT NULL,
 `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `language_id` (`language_id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `sourcecodes_tags` (
 `sourcecode_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`sourcecode_id`),
 KEY `tag_id` (`tag_id`),
 CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Было бы здорово, если бы кто-нибудь мог рассказать мне, что здесь происходит, у меня не было формального обучения или чего-то еще с MySQL:)

Спасибо.

Изменить: Это код, который генерирует ошибку:

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

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.

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.

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.

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.

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

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:

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:

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:

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.

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

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:

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:

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:

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.