Foreign key constraint failed ошибка - Не ошибается лишь тот, кто ничего не делает!

I’ve set up two tables:

CREATE TABLE A
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT
 );

CREATE TABLE B
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    id2 INTEGER,
    book TEXT,
    FOREIGN KEY(id2) REFERENCES A(id)
);

After I insert data into A, it looks like this:

1    John

2    Amy

3    Peter

After I insert data into B, it looks like this:

1     1     Lord of the Rings

2     1     Catch 22

3     2     Sum of All Fears

4     3     Hunt for Red October

I then execute the following statement:

delete from a where id=1;

I get the following: "Error: foreign key constraint failed"

I then restart sqlite3 and try again but this time I enter this first:

PRAGMA foreign_keys = 1;

it still doesn’t work……

Источник

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! 👍

Источник

Finding out why Foreign key creation fail

When MySQL is unable to create a Foreign Key, it throws out this generic error message:

ERROR 1215 (HY000): Cannot add foreign key constraint

– The most useful error message ever.

Fortunately, MySQL has this useful command that can give the actual reason about why it could not create the Foreign Key.

mysql> SHOW ENGINE INNODB STATUS;

That will print out lots of output but the part we are interested in is under the heading ‘LATEST FOREIGN KEY ERROR’:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-29 13:40:56 0x7f3cb452e700 Error in foreign key constraint of table test_database/my_table:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT idx_name FOREIGN KEY (employee_id) REFERENCES employees (id)
The index in the foreign key in table is idx_name
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This output could give you some clue about the actual reason why MySQL could not create your Foreign Key

Reason #1 – Missing unique index on the referenced table

This is probably the most common reason why MySQL won’t create your Foreign Key constraint. Let’s look at an example with a new database and new tables:

In the all below examples, we’ll use a simple ‘Employee to Department” relationship:

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.07 sec)

As you may have noticed, we have not created the table with PRIMARY KEY or unique indexes. Now let’s try to create Foreign Key constraint between employees.department_id column and departments.id column:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Let’s look at the detailed error:

mysql> SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-31 09:25:13 0x7fddc805f700 Error in foreign key constraint of table foreign_key_1/#sql-5ed_49b:
FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This is because we don’t have any unique index on the referenced table i.e. departments. We have two ways of fixing this:

Option 1: Primary Keys

Let’s fix this by adding a primary key departments.id

mysql> ALTER TABLE departments ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Option 2: Unique Index

mysql> CREATE UNIQUE INDEX idx_department_id ON departments(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

Reason #2 – Different data types on the columns

MySQL requires the columns involved in the foreign key to be of the same data types.

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id char(20),
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

You may have noticed that employees.department_id is int while departments.id is char(20). Let’s try to create a foreign key now:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Let’s fix the type of departments.id and try to create the foreign key again:

mysql> ALTER TABLE departments MODIFY id INT;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #3 – Different collation/charset type on the table

This is a surprising reason and hard to find out. Let’s create two tables with different collation (or also called charset):

Let’s start from scratch to explain this scenario:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=latin1;
Query OK, 0 rows affected (0.08 sec)

You may notice that we are using a different character set (utf8 and latin1` for both these tables. Let’s try to create the foreign key:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

It failed because of different character sets. Let’s fix that.

mysql> SET foreign_key_checks = 0; ALTER TABLE departments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

If you have many tables with a different collation/character set, use this script to generate a list of commands to fix all tables at once:

mysql --database=your_database -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}'

Reason #4 – Different collation types on the columns

This is a rare reason, similar to reason #3 above but at a column level.

Let’s try to reproduce this from scratch:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id char(26) CHARACTER SET utf8,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE departments(
    ->     id char(26) CHARACTER SET latin1,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

We are using a different character set for employees.department_id and departments.id (utf8 and latin1). Let’s check if the Foreign Key can be created:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Nope, as expected. Let’s fix that by changing the character set of departments.id to match with employees.department_id:

mysql> ALTER TABLE departments MODIFY id CHAR(26) CHARACTER SET utf8;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #5 -Inconsistent data

This would be the most obvious reason. A foreign key is to ensure that your data remains consistent between the parent and the child table. So when you are creating the foreign key, the existing data is expected to be already consistent.

Let’s setup some inconsistent data to reproduce this problem:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

Let’s insert a department_id in employees table that will not exist in departments.id:

mysql> INSERT INTO employees VALUES (1, 'Amber', 145);
Query OK, 1 row affected (0.01 sec)

Let’s create a foreign key now and see if it works:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foreign_key_1`.`#sql-5ed_49b`, CONSTRAINT `fk_department_id` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`))

This error message is atleast more useful. We can fix this in two ways. Either by adding the missing department in departments table or by deleting all the employees with the missing department. We’ll do the first option now:

mysql> INSERT INTO departments VALUES (145, 'HR');
Query OK, 1 row affected (0.00 sec)

Let’s try to create the Foreign Key again:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 1 row affected (0.24 sec)
Records: 1  Duplicates: 0  Warnings: 0

It worked this time.

So we have seen 5 different ways a Foreign Key creation can fail and possible solutions of how we can fix them. If you have encountered a reason not listed above, add them in the comments.

If you are using MySQL 8.x, the error message will be a little different:

SQLSTATE[HY000]: General error: 3780 Referencing column 'column' and referenced column 'id' in foreign key constraint 'idx_column_id' are incompatible.

Источник

Группа:
Администратор
Сообщений:
8995
Регистрация:
20.02.2010

04.03.2011 01:05

Для многих своих таблиц Magento старается поддерживать целостность (непротиворечивость) данных между таблицами, и для этих целей использует ограничения для значений внешних ключей.
Выглядит это примерно так:

ALTER TABLE `directory_country_region_name`
	ADD CONSTRAINT `FK_DIRECTORY_REGION_NAME_REGION` 
		FOREIGN KEY (`region_id`) 
		REFERENCES `directory_country_region` (`region_id`) 
		ON DELETE CASCADE 
		ON UPDATE CASCADE
;

Если вы обновляете данные напрямую через SQL, вы можете столкнуться с сообщением «Foreign key constraint fails».
Сообщение означает, что обновляя записи одной таблицы, вы разорвали созданную командой «ADD CONSTRAINT» связь между её значениями и значениями другой таблицы, и MySQL видит, что целостность данных нарушена.
Соответственно, MySQL откажется выполнить вашу операцию и сообщит об ошибке «Foreign key constraint fails».

Если вы сознательно временно нарушаете целостность данных, то временно отключите проверку внешних ключей командой:

SET FOREIGN_KEY_CHECKS=0;

После выполнения команд, нарушающих ссылочную целостность и команд, восстанавливающих ссылочную целостность, верните контроль ссылочной целостности обратной командой:

SET FOREIGN_KEY_CHECKS=1;

Источник

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.

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.

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.

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

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

Источник

Disabling the foreign key check

Finding out why Foreign key creation fail

Reason #1 – Missing unique index on the referenced table

Reason #2 – Different data types on the columns

Reason #3 – Different collation/charset type on the table

Reason #4 – Different collation types on the columns

Reason #5 -Inconsistent data

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

1. Adding the Value in the Referenced Table

2. Disable the Foreign Key Control

Conclusion

Не пропустите эти материалы по теме: