Mysql 5.6 foreign key constraint error; did not happen in 5.5

Question

Used tables:

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' 

(there were no errors in mysql 5.5)

The only way to make it work:

 SET foreign_key_checks = 0; 

What has changed in 5.6? This is mistake?

Answer 1

This is apparently an improvement in MySQL 5.6, although the implementation seems too strict.

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

Starting from 5.6.7, the server prohibits changes to the foreign key columns, which can lead to a loss of referential integrity. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... Then add FOREIGN KEY.

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

This is beautiful, however ...

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

The corresponding columns of the foreign key and reference key must have the same data types. The size and sign of integer types must be the same. The length of string types does not have to be the same. For non-non-character (character) string columns, the character set and sorting must be the same.

---

Your comment:

This is not a contradiction. You can still create foreign keys in MySQL 5.6 with different string strings.

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

You can change columns if there is no possibility of truncating data.

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

But you cannot change columns if they can 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 find, you can turn off foreign key checking either with foreign_check_checks=0 , or by removing a constraint with ALTER TABLE, and then recreate the constraint after changing the column.