mysql 5.6 外键约束错误； 5.5中没有出现

涉及表：

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_模块

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'

（在mysql 5.5中没有错误）

我让它发挥作用的唯一方法是：

SET foreign_key_checks = 0;

5.6 中发生了什么变化？这是一个错误吗？

这显然是 MySQL 5.6 的改进，尽管实现似乎有点过于严格。

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

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

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

不过这样也好……

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

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

回复您的评论：

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

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

只要不会截断数据，您就可以修改列。

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

但如果列可能会丢失数据，则您无法对其进行修改。

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'

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