Description:
The documentation regarding RENAME table DDL and CONSTRAINTS seems to clearly say that foreign keys will not be reconstructed to reference the new table name unless they meet a specific string requirement:
### https://dev.mysql.com/doc/refman/5.7/en/rename-table.html
RENAME TABLE changes internally generated foreign key constraint names and user-defined foreign key constraint names that contain the string “tbl_name_ibfk_” to reflect the new table name. InnoDB interprets foreign key constraint names that contain the string “tbl_name_ibfk_” as internally generated names.
Foreign keys that point to the renamed table are not automatically updated. In such cases, you must drop and re-create the foreign keys in order for them to function properly.
###
This does not seem to be the case, foreign key constraints seem to be rebuilt regardless of constraint name.
I was able to reproduce this on 5.7 and 5.5, MacOS Sierra and REHL Maipo respectively.
Am I reading the documentation wrong?
Cheers,
Dov Endress
How to repeat:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.00 sec)
mysql> create database testfkconstraint
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> use testfkconstraint
Database changed
mysql> create table parent( id tinyint not null auto_increment primary key);
Query OK, 0 rows affected (0.03 sec)
mysql> create table child( id tinyint not null auto_increment primary key, parentid tinyint not null default 0, CONSTRAINT not_a_reserved_string FOREIGN KEY (parentid) REFERENCES parent (id) ) ;
Query OK, 0 rows affected (0.03 sec)
mysql> show create table parent\G
*************************** 1. row ***************************
Table: parent
Create Table: CREATE TABLE `parent` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
`parentid` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `not_a_reserved_string` (`parentid`),
CONSTRAINT `not_a_reserved_string` FOREIGN KEY (`parentid`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> rename table parent to parent_table_renamed;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
`parentid` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `not_a_reserved_string` (`parentid`),
CONSTRAINT `not_a_reserved_string` FOREIGN KEY (`parentid`) REFERENCES `parent_table_renamed` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
Suggested fix:
Re-write the documentation to reflect actual behavior.
