我有点奇怪的问题。我正尝试将一个外键添加到一个引用另一个表的表中,但由于某种原因,它失败了。由于我对MySQL的了解有限,唯一可能令人怀疑的是,在另一个表上有一个外键引用了我试图引用的那个外键。
我已经在两个表上执行了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`) 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
发布于 2012-08-28 22:04:10
我在三次不同的时候遇到了完全相同的问题。在每个实例中,这都是因为我的一个(或多个)记录不符合新的外键。在尝试添加键本身之前,您可能希望更新现有记录以遵循外键的语法约束。以下示例通常应隔离问题记录:
SELECT * FROM (tablename)
WHERE (candidate key) <> (proposed foreign key value)
AND (candidate key) <> (next proposed foreign key value)
在查询中对外键中的每个值重复AND (candidate key) <> (next proposed foreign key value)
。
如果您有大量记录,这可能会很困难,但如果您的表相当小,则不会花费太长时间。我对SQL语法不是很在行,但对我来说,这始终是一个孤立的问题。
https://stackoverflow.com/questions/1253459
复制相似问题