MySQL 如何更新mysql数据库中的外键值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7541446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update foreign key value in mysql database
提问by Primoz Rome
I have three tables: categories, languages and categories_languages. Categories_languages is many to many table which links together categories and languages. I would like to update a foregin key value in table languages but it throws me error #1451 - Cannot delete or update a parent row: a foreign key constraint fails!
我有三个表：类别、语言和类别语言。Categories_languages 是将类别和语言链接在一起的多对多表。我想更新表语言中的外键值，但它向我抛出错误 #1451 - 无法删除或更新父行：外键约束失败！
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
`modified` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `languages` (
`id` char(2) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `categories_languages` (
`id` int(11) unsigned NOT NULL auto_increment,
`category_id` int(11) unsigned NOT NULL,
`language_id` char(2) NOT NULL,
`translation` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_category_id_language_id` (`category_id`,`language_id`),
KEY `fk_language_id` (`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
ALTER TABLE `categories_languages`
ADD CONSTRAINT `categories_languages_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `categories_languages_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE;
The error is clear to me, but how can I update a key value in this case? I tried adding ON UPDATA CASCADE:
错误对我来说很清楚，但是在这种情况下我如何更新键值？我尝试添加 ON UPDATA CASCADE：
ALTER TABLE `categories_languages`
ADD CONSTRAINT `categories_languages_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `categories_languages_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
but that also fails with message: MySQL said: Documentation #1005 - Can't create table './db_dodo/#sql-c2f_80e6f.frm' (errno: 121)
但这也失败并显示消息：MySQL 说：文档 #1005 - 无法创建表 './db_dodo/#sql-c2f_80e6f.frm' (errno: 121)
回答by RavuAlHemio
You can temporarily suspend foreign key checking:
您可以暂时暂停外键检查：
SET foreign_key_checks = 0;
UPDATE languages SET id='xyz' WHERE id='abc';
UPDATE categories_languages SET language_id='xyz' WHERE language_id='abc';
SET foreign_key_checks = 1;
EDIT: As for the foreign key problem: is the data stored on a local or a remote file system? errno121 is EREMOTEIO(Remote I/O error). Perhaps there are permission problems on the target file system or it doesn't support the #character in filenames?
编辑：至于外键问题：数据是存储在本地还是远程文件系统上？errno121 是EREMOTEIO（远程 I/O 错误）。也许目标文件系统存在权限问题，或者它不支持#文件名中的字符？
回答by hshahdoost
If you are looking for a temp solution you can also change the ON UPDATEaction to CASCADEand modify your ids
如果您正在寻找临时解决方案，您还可以将ON UPDATE操作更改为CASCADE并修改您的 ID
