mysql 5.6外键约束错误; 5.5没有发生 [英] mysql 5.6 foreign key constraint error; didn't occur in 5.5

问题描述

phppos_permissions_actions：

  mysql>显示创建表phppos_permissions_actions; 
 + ---------------------------- + ---------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------ + 
 |表|创建表| 
 + ---------------------------- + ---------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------ + 
 | phppos_permissions_actions | CREATE TABLE`phppos_permissions_actions`（
`module_id` varchar（255）COLLATE utf8_unicode_ci NOT NULL，
`person_id` int（11）NOT NULL，
`action_id` varchar（255）COLLATE utf8_unicode_ci NOT NULL，
 PRIMARY KEY（`module_id`，`person_id`，`action_id`），
 key`phppos_permissions_actions_ibfk_2`（`person_id`），
 KEY`phppos_permissions_actions_ibfk_3`（`action_id`） 
 CONSTRAINT`phppos_permissions_actions_ibfk_1` FOREIGN KEY（`module_id`）参考`phppos_modules`（`module_id`），
 CONSTRAINT`phppos_permissions_actions_ibfk_2` FOREIGN KEY（`person_id`）REFERENCES`phppos_employees`（`person_id`）， 
 CONSTRAINT`phppos_permissions_actions_ibfk_3` FOREIGN KEY（`action_id`）REFERENCES`phppos_modules_actions`（`action_id`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci | 
 + ---------------------------- + ---------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------ + 
  

phppos_modules

  mysql>显示创建表phppos_modules; 
 + ---------------- + ---------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------- + 
 |表|创建表| 
 + ---------------- + ---------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------- + 
 | phppos_modules | CREATE TABLE`phppos_modules`（
`name_lang_key` varchar（255）COLLATE utf8_unicode_ci NOT NULL，
`desc_lang_key` varchar（255）COLLATE utf8_unicode_ci NOT NULL，
`sort` int（10）NOT NULL，
`module_id` varchar（255）COLLATE utf8_unicode_ci NOT NULL，
 PRIMARY KEY（`module_id`），
 UNIQUE KEY`desc_lang_key`（`desc_lang_key`），
 UNIQUE KEY`name_lang_key`（`name_lang_key`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci | 
 + ---------------- + ---------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------- + 
  

查询：

$ b （100）CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL，CHANGE`action_id`` action_id` VARCHAR（100）CHARACTER（100）CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL

错误：

< pre $ ＃1832 - 无法更改列'module_id'：用于外键约束'phppos_permissions_actions_ibfk_1'


（在MySQL 5.5 th我不能错过）

我可以做的唯一方法就是：

pre > SET foreign_key_checks = 0;


5.6中有什么改变？这是一个错误吗？

解决方案

这显然是MySQL 5.6的一个改进，尽管实现看起来有点太严格了。 p>

http：//dev.mysql .com / doc / refman / 5.6 / en / alter-table.html 表示：

从5.6.7开始，服务器禁止更改外键列，这可能会导致引用完整性的丢失。解决方法是在更改列定义和ALTER TABLE ... ADD FOREIGN KEY之前使用ALTER TABLE ... DROP FOREIGN KEY。

<发行说明说这与 http://bugs.mysql.com/bug.php?id= 46599

没问题，但是...

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign- keys.html 表示：

外键和引用键中的相应列必须具有相似的数据类型。整数类型的大小和符号必须相同。 字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。

---

重新评论：

这并不矛盾。您可以在MySQL 5.6中创建不同字符串长度的外键。

  create table foo（p varchar（20）primary key） ; 
创建表格栏（f varchar（10），外键（f）引用foo（p））; 
  

只要没有截断数据的机会，您就可以修改列。 b

  alter table bar修改列f varchar（20）; / *增加字符串长度* / 
查询OK 
  

它可能会丢失数据。

  alter table bar修改列f varchar（5）; / *减少字符串长度* / 
错误1832（HY000）：无法更改列'f'：在外键约束'bar_ibfk_1'中使用
 
  

正如您发现的那样，您可以使用 foreign_check_checks = 0 禁用外键检查，或者通过删除使用ALTER TABLE约束，然后在修改列后重新创建约束。

Tables involved:

phppos_permissions_actions:

mysql> show create table phppos_permissions_actions;
+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_permissions_actions | CREATE TABLE `phppos_permissions_actions` (
  `module_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `person_id` int(11) NOT NULL,
  `action_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`module_id`,`person_id`,`action_id`),
  KEY `phppos_permissions_actions_ibfk_2` (`person_id`),
  KEY `phppos_permissions_actions_ibfk_3` (`action_id`),
  CONSTRAINT `phppos_permissions_actions_ibfk_1` FOREIGN KEY (`module_id`) REFERENCES `phppos_modules` (`module_id`),
  CONSTRAINT `phppos_permissions_actions_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `phppos_employees` (`person_id`),
  CONSTRAINT `phppos_permissions_actions_ibfk_3` FOREIGN KEY (`action_id`) REFERENCES `phppos_modules_actions` (`action_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

phppos_modules

mysql> show create table phppos_modules;
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_modules | CREATE TABLE `phppos_modules` (
  `name_lang_key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `desc_lang_key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `sort` int(10) NOT NULL,
  `module_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`module_id`),
  UNIQUE KEY `desc_lang_key` (`desc_lang_key`),
  UNIQUE KEY `name_lang_key` (`name_lang_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Query:

ALTER TABLE `phppos_permissions_actions` CHANGE `module_id` `module_id` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, CHANGE `action_id` `action_id` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL

Error:

#1832 - Cannot change column 'module_id': used in a foreign key constraint 'phppos_permissions_actions_ibfk_1'

(in mysql 5.5 there was no error)

The only way I can get it to work is by doing:

SET foreign_key_checks = 0;

What changed in 5.6? Is this a bug?

解决方案

This is apparently an improvement in MySQL 5.6, though the implementation seems a little too strict.

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html says:

As of 5.6.7, the server prohibits changes to foreign key columns with the potential to cause loss of referential integrity. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward.

The release notes say this is related to http://bugs.mysql.com/bug.php?id=46599

That's fine, however...

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html says:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

---

Re your comment:

It's not really a contradiction. You can still create foreign keys in MySQL 5.6 with different string lengths.

create table foo ( p varchar(20) primary key );
create table bar ( f varchar(10), foreign key (f) references foo (p) );

You can modify columns as long as there's no chance of truncating data.

alter table bar modify column f varchar(20); /* increasing string length */
Query OK

But you can't modify columns if it might lose data.

alter table bar modify column f varchar(5); /* decreasing string length */
ERROR 1832 (HY000): Cannot change column 'f': 
used in a foreign key constraint 'bar_ibfk_1'

And as you discovered, you can disable foreign key checks either with foreign_check_checks=0 or else by dropping the constraint with ALTER TABLE and then recreate the constraint after you modify your column.

这篇关于mysql 5.6外键约束错误; 5.5没有发生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！