Bug #86030 rename table constraint behavior mis-documented
Submitted: 20 Apr 2017 22:58 Modified: 30 Jul 2017 23:05
Reporter: Dov Endress Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7.17, 5.7.18 OS:Any
Assigned to: CPU Architecture:Any

[20 Apr 2017 22:58] Dov Endress
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.
[24 Apr 2017 11:35] MySQL Verification Team
Hello Dov Endress,

Thank you for the report and feedback.

Thanks,
Umesh
[30 Jul 2017 23:05] Paul DuBois
Posted by developer:
 
Page updated.