Mysql错误1452-无法添加或更新子行：外键约束失败 [英] Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

问题描述

我有一个奇怪的问题。我试图添加一个外键引用另一个表，但由于某种原因失败。由于我对MySQL的知识有限，唯一可能会怀疑的是在引用我想引用的表上有一个不同的表上有一个外键。

这是我的表格关系的图片，通过phpMyAdmin生成：
关系

我在这两个表上做了一个 SHOW CREATE TABLE 查询， sourcecodes_tags 是带有外键的表， sourcecodes 是引用表。

  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 ``）参考`类`（```）在删除级联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`ta gs`（`id`）ON DELETE CASCADE ON UPDATE CASCADE 
）ENGINE = InnoDB DEFAULT CHARSET = latin1 
  

$ b $如果有人能告诉我这里发生了什么事情，我还没有接受过任何正式的培训，或者没有任何与MySQL有关的事情。）

/ p>

编辑：这是产生错误的代码：

  ALTER TABLE sourcecodes_tags ADD FOREIGN KEY（sourcecode_id）REFERENCES sourcecodes（id）ON DELETE CASCADE ON UPDATE CASCADE 
  

sourcecodes_tags 表很可能包含 sourcecode_id 值不再存在于源代码表中。

这是一个查询，可以找到这些ID：

  SELECT DISTINCT sourcecode_id FROM 
 sourcecodes_tags标记LEFT JOIN源代码sc ON tags.sourcecode_id = sc.id 
 WHERE sc.id IS NULL; 
  

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

解决方案

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;

这篇关于Mysql错误1452-无法添加或更新子行：外键约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！