Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

Question

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

Solution 1

Quite likely your sourcecodes_tags table contains sourcecode_id values that no longer exists in your sourcecodes table. You have to get rid of those first.

Here's a query that can find those IDs:

SELECT DISTINCT sourcecode_id FROM 
   sourcecodes_tags tags LEFT JOIN sourcecodes sc ON tags.sourcecode_id=sc.id 
WHERE sc.id IS NULL;

Solution 2

I had the same issue with my MySQL database but finally, I got a solution which worked for me.
Since in my table everything was fine from the mysql point of view(both tables should use InnoDB engine and the datatype of each column should be of the same type which takes part in foreign key constraint).
The only thing that I did was to disable the foreign key check and later on enabled it after performing the foreign key operation.
Steps that I took:

SET foreign_key_checks = 0;

alter table tblUsedDestination add constraint f_operatorId foreign key(iOperatorId) references tblOperators (iOperatorId); Query
OK, 8 rows affected (0.23 sec) Records: 8  Duplicates: 0  Warnings: 0

SET foreign_key_checks = 1;

Solution 3

Use NOT IN to find where constraints are constraining:

SELECT column FROM table WHERE column NOT IN 
(SELECT intended_foreign_key FROM another_table)

so, more specifically:

SELECT sourcecode_id FROM sourcecodes_tags WHERE sourcecode_id NOT IN 
(SELECT id FROM sourcecodes)

EDIT: IN and NOT IN operators are known to be much faster than the JOIN operators, as well as much easier to construct, and repeat.

Solution 4

Truncate the tables and then try adding the FK Constraint.

I know this solution is a bit awkward but it does work 100%. But I agree that this is not an ideal solution to deal with problem, but I hope it helps.

Solution 5

For me, this problem was a little different and super easy to check and solve.

You must ensure BOTH of your tables are InnoDB. If one of the tables, namely the reference table is a MyISAM, the constraint will fail.

    SHOW TABLE STATUS WHERE Name =  't1';

    ALTER TABLE t1 ENGINE=InnoDB;

View more solutions

Comments

[Zed]

could you also post your insert/update command which results in the error?

[Zed]

are your tables empty when you add this foreign key?

[Zed]

try running this query to see if there is any sourcecode_id that is not a real id: SELECT sourcecode_id FROM sourcecodes_tags WHERE sourcecode_id NOT IN (SELECT id FROM sourcecodes AS tmp);

[theblackpearl]

Why does it fail if table is empty?

[khargoosh]

@Zim the link to your picture is broken 404

[MeirG]

Added by mistake. Sorry!

[Torben]

No need to truncate everything. "UPDATE sourcecodes_tags SET sourcecode_id = NULL WHERE sourcecode_id NOT IN (SELECT id FROM sourcecodes)" should suffice. Or if null is not allowed in "sourcecode_id", then remove those rows or add those missing values to the "sourcecodes" table.

[François Breton]

Sometimes, if data increment the autoincrement PK, it forces you to truncate.

[cs_alumnus]

foreign_key_checks are there for a reason. If you can't add the foreign key because it violates the constraint, you should correct the data first. Turning off checks then adding the key leaves you in an inconsistent state. Foreign key checks add overhead, if you do not want to use them, then use myisam instead.

[MizAkita]

@ShankarDamodaran not sure why truncating the table works but this solution worked well for me. I was able to get my relationships to work... THANKS!

[Vincent]

So, if I understand this correctly, we can add a foreign key to a table that already has data in it, but only if a child row exists for each row in the parent table? If there are no child rows for each row in the parent table (which is what your query discovers) then the foreign key script will fail.

[xlecoustillier]

VHanded gave the same answer 3 years ago. Let's hope there wasn't any important data in the tables...

[Armfoot]

@AbuSadatMohammedYasin no it shouldn't: the question asked "what is going on" and this answer simply doesn't attempt to explain it. As cs_alumnus mentioned, there's a bigger problem: all the new values that should be referencing another value in the other table (as a Foreign key should do) may point to nothing, creating an inconsistent state. Cayetano's short and effective explanation allows you to find which values you should update before creating the constraint so you won't be surprised by queries that should return values that should exist!

[Armfoot]

@Vincent if by parent table you mean the table being referenced, then yes! Therefore with Cayetano's select you get all rows you need to update/remove from your "child" table before you add the new constraint (FK). Once they all point to values in the "another_table" then you're good to go!

[Armfoot]

@MizAkita it works because it deletes the rows that have no corresponding value in the other table, allowing the new constraint to be created. If you just find those rows and update or delete them (like Cayetano's suggestion), you don't need to delete the other rows...

[naXa stands with Ukraine]

UPDATE sourcecodes_tags SET sourcecode_id = NULL WHERE sourcecode_id NOT IN (SELECT id FROM sourcecodes) should help to get rid of those IDs. Or if null is not allowed in sourcecode_id, then remove those rows or add those missing values to the sourcecodes table.

[CesareoAguirre]

mysql> insert into email (email) values ('[email protected]'); mysql> insert into ndtc (ndtc, year, month) values ('1111222233334444', '2000', '01'); mysql> insert into cliente (nombres, apellidos, telefono, idNDTC, idEmail) values ('myname', 'myapp', '5555555555', 1,1);

[Krewetka]

@Armfoot - I had this problem when adding first row to the table with foreign key. So I had no rows to search for.

[Meloman]

I was thinking the same but, for me SELECT Tchild.id FROM Tchild INNER JOIN Tmain ON Tmain.id = Tchild.fk_id WHERE Tmain.id IS NULL doesn't return anything, so the problem is elsewhere !?

[Meloman]

That was the only solution working for me... but 36MB of data need to be imported again.

[Ryan]

Ahh this was the problem for me. I was trying to run UPDATE `homestead`.`automations` SET `deleted_at`=NULL WHERE deleted_at IS NOT NULL;, which didn't involve a foreign key at all, so I was confused. But the fact that my contacts table was missing some records that the automations table referred to caused it to throw this "Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails".

[Omar Dulaimi]

In my case the parent had column length 25 chars while the children tables had 30 chars. Increasing chars length to 30 fixed it for me. Thanks for pointing me in the right direction.