mysql 5.6 外键约束错误；没有发生在 5.5 [英] mysql 5.6 foreign key constraint error; didn't occur in 5.5
问题描述
涉及的表:
phppos_permissions_actions:
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
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 |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
查询:
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
错误:
#1832 - Cannot change column 'module_id': used in a foreign key constraint 'phppos_permissions_actions_ibfk_1'
(在 mysql 5.5 中没有错误)
(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;
5.6 有什么变化?这是一个错误吗?
What changed in 5.6? Is this a bug?
推荐答案
这显然是 MySQL 5.6 的改进，尽管实现似乎有点过于严格.
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 说:
从 5.6.7 开始，服务器禁止更改可能导致参照完整性丢失的外键列.一种解决方法是在更改列定义之前使用 ALTER TABLE ... DROP FOREIGN KEY，然后使用 ALTER TABLE ... ADD FOREIGN KEY.
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.
发行说明说这与http://bugs.mysql.com/bug.php?id有关=46599
没关系，不过……
http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html 说:
外键和被引用键中对应的列必须具有相似的数据类型.整数类型的大小和符号必须相同.字符串类型的长度不必相同.对于非二进制(字符)字符串列，字符集和排序规则必须相同.
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:
这并不矛盾.您仍然可以在 MySQL 5.6 中使用不同的字符串长度创建外键.
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'
正如您所发现的，您可以使用 foreign_check_checks=0 禁用外键检查，或者通过使用 ALTER TABLE 删除约束，然后在修改列后重新创建约束.
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屋！
