Mysql error 1452 - Cannot add or update a child ro

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.

Here is a picture of my table relationships, generated via phpMyAdmin: Relationships

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

It would be great if anyone could tell me what is going on here, I've had no formal training or anything with MySQL :)

Thanks.

Edit: 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

Answer 1

It seems there are some invalid value for the column like 0 that is not a valid foreign key so MySQL cannot set foreign key constraint for it.

You can follow these steps:

1. Drop the column which you have tried to set FK constraint for.

2. Add it again and set its default value as NULL.

3. Try to set foreign key constraint for it again.

Answer 2

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.

Answer 3

Make sure the value is in the other table otherwise you will get this error, in the assigned corresponding column.

So if it is assigned column is assigned to a row id of another table , make sure there is a row that is in the table otherwise this error will appear.

Answer 4

I'd the same problem,I checked rows of my tables and found there were some incompatibility with value of fields that I wanted to define as foreign key. I corrected those value, tried again and problem was solved.

Answer 5

I was readying this solutions and this example may help.

My database have two tables (email and credit_card) with primary keys for their IDs. Another table (client) refers to this tables IDs as foreign keys. I have a reason to have the email apart from the client data.

First I insert the row data for the referenced tables (email, credit_card) then you get the ID for each, those IDs are needed in the third table (client).

If you don't insert first the rows in the referenced tables, MySQL wont be able to make the correspondences when you insert a new row in the third table that reference the foreign keys.

If you first insert the referenced rows for the referenced tables, then the row that refers to foreign keys, no error occurs.

Hope this helps.

Answer 6

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.