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

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 mysql point of view(both table should use Innodb engine and the datatype of each column should be of same type which take part in foreign key constraint).
Only thing that I did was to disable the foreign key check and later on enabled it after performing foreign key operation.
Steps that I took:

mysql> SET foreign_key_checks = 0;

mysql> 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

mysql> SET foreign_key_checks = 1;

Answer 2

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.

Answer 3

I had this exact same problem about three different times. In each instance it was because one (or more) of my records did not conform to the new foreign key. You may want to update your existing records to follow the syntax constraints of the foreign key before trying to add the key itself. The following example should generally isolate the problem records:

SELECT * FROM (tablename)
    WHERE (candidate key) <> (proposed foreign key value) 
        AND (candidate key) <> (next proposed foreign key value)

repeat AND (candidate key) <> (next proposed foreign key value) within your query for each value in the foreign key.

If you have a ton of records this can be difficult, but if your table is reasonably small it shouldn't take too long. I'm not super amazing in SQL syntax, but this has always isolated the issue for me.

Answer 4

I have a solution, you just need to answer one question:

Is your table already storing data? Especially the table included foreign key.

If the answer is yes, then the only thing you do is delete all the record and then you are free to add in any foreign key in your table.

Delete instruction: From child(which include foreign key table) to parent table.

The reason you cannot add in foreign key after data entries is due to the table inconsistency, what are you going to deal with new foreign key on former data filled table?

If no, then follow others instructions.

Answer 5

I had the same problem today. I tested for four things, some of them already mentioned here:

1. Are there any values in your child column that don't exist in the parent column (besides NULL, if the child column is nullable)

2. Do child and parent columns have the same datatype?

3. Is there an index on the parent column you are referencing? MySQL seems to require this for performance reasons (http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html)

4. And this one solved it for me: Do both tables have identical collation?

I had one table in utf-8 and the other in iso-something. That didnt't work. After changing the iso-table to utf-8 collation the constraints could be added without problems. In my case, phpMyAdmin didn't even show the child table in iso-encoding in the dropdown for creating the foreign key constraint.

Answer 6

try this

SET foreign_key_checks = 0;

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

SET foreign_key_checks = 1;