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 problem and found solution, placing NULL instead of NOT NULL on foreign key column. Here is a query:

ALTER TABLE `db`.`table1`
ADD COLUMN `col_table2_fk` INT UNSIGNED NULL,
ADD INDEX `col_table2_fk_idx` (`col_table2_fk` ASC),
ADD CONSTRAINT `col_table2_fk1`
FOREIGN KEY (`col_table2_fk`)
REFERENCES `db`.`table2` (`table2_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

MySQL has executed this query!

Answer 2

Empty both your tables' data and run the command. It will work.

Answer 3

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;

Answer 4

I was getting this error when using Laravel and eloquent, trying to make a foreign key link would cause a 1452. The problem was lack of data in the linked table.

Please see here for an example: http://mstd.eu/index.php/2016/12/02/laravel-eloquent-integrity-constraint-violation-1452-foreign-key-constraint/

Answer 5

you can try this exapmple

 START TRANSACTION;
 SET foreign_key_checks = 0;
 ALTER TABLE `job_definers` ADD CONSTRAINT `job_cities_foreign` FOREIGN KEY 
 (`job_cities`) REFERENCES `drop_down_lists`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 SET foreign_key_checks = 1;
 COMMIT;

Note : if you are using phpmyadmin just uncheck Enable foreign key checks

as example

hope this soloution fix your problem :)

Answer 6

This also happens when setting a foreign key to parent.id to child.column if the child.column has a value of 0 already and no parent.id value is 0

You would need to ensure that each child.column is NULL or has value that exists in parent.id

And now that I read the statement nos wrote, that's what he is validating.