Cannot add or update a child row a foreign key constraint fails как исправить - Исправление ошибок и поиск оптимальных решений проблем

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

Источник

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

Источник

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

Источник

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

Position Is Everything: Your Go-To Resource for Learn & Build: CSS,JavaScript,HTML,PHP,C++ and MYSQL.

Источник

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.

Источник

In MySQL, Enter a row in the child table without having row in the parent table

Example of insert row in Child table without having a row in Parent table MySQL

Create the table Parent P1 and Child C1.

mysql> create table p1(id integer primary key, name varchar(100));
Query OK, 0 rows affected (0.09 sec)

mysql> create table c1(cid integer primary key, pid integer, foreign key (pid) references p1(id));
Query OK, 0 rows affected (0.09 sec)

2. Insert data in the Parent and child table and child table throw error due to not presence of data in the parents table.

mysql> insert into p1 values (1,'a');
Query OK, 1 row affected (0.03 sec)

mysql> insert into p1 values (2,'b');
Query OK, 1 row affected (0.01 sec)

mysql> insert into c1 values (2,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`c1`, CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))

3. Disable the foreign key check and enable it.

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into c1 values (3,5);
Query OK, 1 row affected (0.02 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

4. After enabling the foreign key check, the insert query throw error again.

mysql> insert into c1 values (4,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`c1`, CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))

5. Verify the data in both P1 and C1 tables.

mysql> select * from c1;
+-----+------+
| cid | pid  |
+-----+------+
|   3 |    5 |
+-----+------+
1 row in set (0.00 sec)

mysql> select * from p1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

Источник