如何更新mysql数据库中的外键值 [英] How to update foreign key value in mysql database

问题描述

我有三个表：类别，语言和categories_languages。 Categories_languages是多种多样的表，它们将类别和语言连接起来。我想更新表格语言的foregin键值，但它会引发错误＃1451 - 无法删除或更新父行：外键约束失败！

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' b $ b`name` varchar（20）NOT NULL，
`modified` int（10）unsigned NOT NULL，
PRIMARY KEY（`id`），
）ENGINE = InnoDB DEFAULT CHARSET = UTF8;

CREATE TABLE IF NOT EXISTS`语言`（
`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，$ b $``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;
$ b $ 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`语言`（`id`）ON DELETE CASCADE;

这个错误对我来说很清楚，但是如何在这种情况下更新一个键值呢？我尝试添加ON UPDATA CASCADE：

pre $ ALTER TABLE`categories_languages`
ADD CONSTRAINT`categories_languages_ibfk_1` FOREIGN KEY（`category_id ``参考`类`（`id`）在DELETE CASCADE ON UPDATE CASCADE上，
ADD CONSTRAINT`categories_languages_ibfk_2` FOREIGN KEY（`language_id`）REFERENCES`语言（`id`）ON DELETE CASCADE ON UPDATE CASCADE;


但是也失败了：MySQL说：文档＃1005 - 无法创建表格。 /db_dodo/#sql-c2f_80e6f.frm'（errno：121）

解决方案

您可以暂时暂停外键检查： p>

  SET foreign_key_checks = 0; 
 UPDATE语言SET id ='xyz'WHERE id ='abc'; 
 UPDATE categories_languages SET language_id ='xyz'WHERE language_id ='abc'; 
 SET foreign_key_checks = 1; 
  

编辑：至于外键问题：一个本地或远程文件系统？ errno 121是 EREMOTEIO （远程I / O错误）。也许目标文件系统有权限问题，或者它不支持文件名中的＃字符？

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!

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:

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)

解决方案

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? errno 121 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?

这篇关于如何更新mysql数据库中的外键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！