Cannot add or update a child row because a foreign key constraint fails [closed]

Question

I have InnoDB tables in MySQL; the parent user table, and a set of child tables that store email addresses, actions, etc. These are tied to the parent user table by a foreign key uid, with all of the parent and child keys being int(10). All child tables have a uid value with a foreign key constraint pointing to user.uid and set to ON DELETE CASCADE and ON UPDATE CASCADE.

When I delete a user from user, all child constrained entries are removed. However, when I attempt to update a user.uid value, it results in the following error, rather than cascading the uid change to the child tables:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`accounts`.`user_email`, CONSTRAINT `user_email_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE)

Removing the key constraint with user_email and attempting to update the value in user results in the same error but for the next alphabetical user child table, so I don't believe it is a table-specific error.

Results from SHOW ENGINE INNODB STATUS:

------------------------ LATEST FOREIGN KEY ERROR ------------------------ 121018 22:35:41 Transaction: TRANSACTION 0 5564387, ACTIVE 0 sec, process no 1619, OS thread id 2957499248 updating or deleting, thread declared inside InnoDB 499 mysql tables in use 1, locked 1 17 lock struct(s), heap size 2496, 9 row lock(s), undo log entries 2 MySQL thread id 3435659, query id 24068634 localhost root Updating UPDATE `accounts`.`user` SET `uid` = '1' WHERE `user`.`uid` = 306 Foreign key constraint fails for table `accounts`.`user_email`: , CONSTRAINT `user_email_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index `uid` tuple: DATA TUPLE: 2 fields; ... A bunch of hex code But in parent table `accounts`.`user`, in index `PRIMARY`, the closest match we can find is record: ... A bunch of hex code

Sidupac · Accepted Answer · 2016-03-18 08:37:06Z

I solved my 'foreign key constraint fails' issues by adding the following code to the start of the SQL code (this was for importing values to a table)

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET NAMES utf8; SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

Then adding this code to the end of the file

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT; SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS; SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION; SET SQL_NOTES=@OLD_SQL_NOTES;

@Timo This tells the database to IGNORE unique and foreign key constraints. Basically, INSERT and UPDATE statements are done bypassing these rules. You could end up messing everything up. ONLY do this if it's for a specific action and you're 100% certain that what you're about to do is really okay.

walrii · Accepted Answer · 2012-10-19 05:32:23Z

Since you haven't given table definitions, it's hard to guess. But it looks like you are attempting to modify the foreign key in the child table. AFAIK, this is illegal, you can modify it from the parent, but not the child table.

Consider this example:

CREATE TABLE parent ( parent_id INT NOT NULL, parent_data int, PRIMARY KEY (parent_id) ) ENGINE=INNODB; CREATE TABLE child1 ( child1_id INT, child1_data INT, fk_parent_id INT, INDEX par_ind1 (fk_parent_id), FOREIGN KEY (fk_parent_id) REFERENCES parent(parent_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB; CREATE TABLE child2 ( child2_id INT, child2_data INT, fk_parent_id INT, INDEX par_ind2 (fk_parent_id), FOREIGN KEY (fk_parent_id) REFERENCES parent(parent_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB; INSERT INTO parent (parent_id, parent_data) VALUES (1, 11), (2, 12); INSERT INTO child1 (child1_id, child1_data, fk_parent_id) VALUES (101, 1001, 1), (102, 1002, 1), (103, 1003, 1), (104, 1004, 2), (105, 1005, 2); INSERT INTO child2 (child2_id, child2_data, fk_parent_id) VALUES (106, 1006, 1), (107, 1007, 1), (108, 1008, 1), (109, 1009, 2), (110, 1010, 2);

Then this is allowed:

UPDATE parent SET parent_id = 3 WHERE parent_id = 2; SELECT * FROM parent; SELECT * FROM child1; SELECT * FROM child2;

But this is not, because it modifies the parent fk from the child table:

UPDATE child1 SET fk_parent_id = 4 WHERE fk_parent_id = 1;

It gets an error very similar to your error:

Cannot add or update a child row: a foreign key constraint fails (`db_2_b43a7`.`child1`, CONSTRAINT `child1_ibfk_1` FOREIGN KEY (`fk_parent_id`) REFERENCES `parent` (`parent_id`) ON DELETE CASCADE ON UPDATE CASCADE):

Thanks. I am updating the parent table - see the query from the dump I posted: UPDATE `accounts`.`user` SET `uid` = '1' WHERE `user`.`uid` = 306. user is the parent table, and triggers this error.
Can you post more info about your schema? Or modify the above example so that you get the same error?
What should we do to make it possible to update Child table foreign key without affecting Parent table
Depends what you want. If you don't want the constraint, don't label it as a foreign key, Or use the reference option NO ACTION. This probably deserves its own question.
Actually there's a way but i don't like it, which is SET foreign_key_checks = 0; then update foreign key then SET foreign_key_checks = 1;

Robin · Accepted Answer · 2019-03-19 13:57:47Z

Even though this is pretty old, just chiming in to say that what is useful in @Sidupac's answer is the FOREIGN_KEY_CHECKS=0.

This answer is not an option when you are using something that manages the database schema for you (JPA in my case) but the problem may be that there are "orphaned" entries in your table (referencing a foreign key that might not exist).

This can often happen when you convert a MySQL table from MyISAM to InnoDB since referential integrity isn't really a thing with the former.

Varun · Accepted Answer · 2013-09-04 11:17:16Z

I had faced same issue while creating foreign constraints on table. the simple way of coming out of this issue are first take backup of your parent and child table then truncate child table and again try to make a relation. hope this will solve the problem.

Unpossible · Accepted Answer · 2013-08-01 18:44:13Z

On an unrelated task, I recently brought up our MySQL database in MySQL Workbench, and when viewing the table relations for the above tables, I noticed 'duplicate' and/or spurious relations that I had somehow missed before (they weren't showing up in PHPMyAdmin FWIW). Removing these extra relations cleared up the issue immediately.

Anna · Accepted Answer · 2017-04-12 10:18:52Z

Such an error on update may be caused by the difference in character set and collation so make sure they are the same for both tables.

LiamJ · Accepted Answer · 2021-02-26 14:56:20Z

My fix for this was my child table needed to be populated before the parent table.

I had two tables: UserDetails and Login linked by an email address. I therefore had to insert into the UserDetails first before inserting into the Login table:

insert into UserDetails (Email, Name, Telephone, Department) values ('Email', 'Name', 'number', 'IT');

Then:

insert into Login (UserID, UserType, Email, Username, Password) VALUES (001, 'SYS-USR-ADMIN', 'Email', 'Name', 'Password')

sulaiman · Accepted Answer · 2019-12-05 04:35:23Z

I've faced this issue and the solution was making sure that all the data from the child field are matching the parent field

for example, you want to add foreign key inside (attendance) table to the column (employeeName)

where the parent is (employees) table, (employeeName) column

all the data in attendance.employeeName must be matching employee.employeeName

Anupam datta · Accepted Answer · 2020-05-08 06:06:29Z

I had the same problem but when I looked closely I found that, it was causing because I was trying to put the foreign key values into the tables before that key was assigned its primary key value. e.g. I had two tables "customers" and "films", "cust_id" and "film_id" were primary key respectively. "customer" had one-to-many relation with "films" so I had "cust_id" as foreign key in "films" tables. But I was trying to put values to "films" table first, so I got that problem.

Skelli · Accepted Answer · 2015-03-28 09:15:25Z

Hope this will assist anyone having the same error while importing CSV data into related tables. In my case the parent table was OK, but I got the error while importing data to the child table containing the foreign key. After temporarily removing the foregn key constraint on the child table, I managed to import the data and was suprised to find some of the values in the FK column having values of 0 (obviously this had been causing the error since the parent table did not have such values in its PK column). The cause was that, the data in my CSV column preceeding the FK column contained commas (which I was using as a field delimeter). Changing the delimeter for my CSV file solved the problem.

Collectives™ on Stack Overflow

Cannot add or update a child row because a foreign key constraint fails [closed]

10 Answers 10

2 Comments

5 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

2 Comments

5 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related