問題描述
我遇到了一個奇怪的問題.我正在嘗試向一個引用另一個表的表添加外鍵，但由于某種原因它失敗了.由于我對 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表.你必須先擺脫那些.
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.
這是一個可以找到這些 ID 的查詢:
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 - 無法添加或更新子行:外鍵約束失敗的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！