问题描述

我遇到了一个奇怪的问题.我正在尝试向一个引用另一个表的表添加外键，但由于某种原因它失败了.由于我对 MySQL 的了解有限，唯一可能怀疑的是在另一个表上有一个外键引用了我试图引用的表.

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.

我对两个表都做了一个 SHOW CREATE TABLE 查询，sourcecodes_tags 是带有外键的表，sourcecodes 是被引用的表.

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

这是产生错误的代码:

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

推荐答案

很可能您的 sourcecodes_tags 表包含 sourcecode_id 值，这些值不再存在于您的 sourcecodes表.你必须先摆脱那些.

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