MySQL Bugs: #72047: Manual does not explain names for indexes generated for FOREIGN keys properly

Bug #72047	Manual does not explain names for indexes generated for FOREIGN keys properly
Submitted:	15 Mar 2014 16:20	Modified:	29 Oct 2014 12:00
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	foreign key, information_schema, innodb_sys_indexes

Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html) explains NAME column of the INFORMATION_SCHEMA.INNODB_SYS_INDEXES as follows:

"The name of the index. User-created indexes have names in all lowercase. Indexes created implicitly by InnoDB have names in all lowercase. The index names are not necessarily unique. Indexes created implicitly by InnoDB have consistent names: PRIMARY for a primary key index, GEN_CLUST_INDEX for the index representing a primary key when one is not specified, ID_IND, FOR_IND for validating a foreign key constraint, and REF_IND."

I've never seen names like ID_IND, FOR_IND or REF_IND in MySQL 5.6 for any other tables but InnoDB's own system tables. At the same time, manual does not explain how InnoDB generate names for foreign key constraints (and indexes created automatically for them). This makes the manual page above misleading and less useful than it can be.

How to repeat:
Create tables like these:

mysql> show create table test.child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table test.parent\G
*************************** 1. row ***************************
       Table: parent
Create Table: CREATE TABLE `parent` (
  `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Then add foreign key constraint without explicit name:

mysql> alter table test.child add constraint foreign key (a) references test.parent (a) ;
Query OK, 0 rows affected (1.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test.child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

We'll see index created implicitly by InnoDB with the name generated based on name of column(s) in the foreign key. This is not explained.

Now, if foreign key constraint has explicit name, it is used as index name also:

mysql> alter table test.child drop foreign key child_ibfk_1;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test.child add constraint fk1 foreign key (a) references test
.parent (a) ;
Query OK, 0 rows affected (2.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test.child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `a` int(11) DEFAULT NULL,
  KEY `fk1` (`a`),
  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_sys_indexes where name like 'fk%'
\G
*************************** 1. row ***************************
INDEX_ID: 494
    NAME: fk1
TABLE_ID: 313
    TYPE: 0
N_FIELDS: 1
 PAGE_NO: 4
   SPACE: 275
1 row in set (0.00 sec)

This is also NOT explained.

Now, names mentioned in the manual are really used:

mysql> select t.table_id, t.name, i.name
    -> from information_schema.innodb_sys_tables t,
    -> information_schema.innodb_sys_indexes i
    -> where t.table_id=i.table_id
    -> and i.name like '%_IND';
+----------+------------------+---------+
| table_id | name             | name    |
+----------+------------------+---------+
|       11 | SYS_FOREIGN      | ID_IND  |
|       11 | SYS_FOREIGN      | FOR_IND |
|       11 | SYS_FOREIGN      | REF_IND |
|       12 | SYS_FOREIGN_COLS | ID_IND  |
+----------+------------------+---------+
4 rows in set (0.04 sec)

but, as you can see above, only for indexes of InnoDB's own internal system/data dictionary tables.

Suggested fix:
Fix the manual to describe the reality of InnoDB indexes naming, with all the details and without misleading statements.

Hello Valeriy,

Thank you for the report.

Thanks,
Umesh

Posted by developer:
 
Regarding this issue: "I've never seen names like ID_IND, FOR_IND or REF_IND in MySQL 5.6 for any other tables but InnoDB's own system tables."

Using the FK linked tables provided above, here is an example of user created tables with FOR_IND, ID_IND, and REF_IND indexes.

mysql> Select t.table_id, t.name, i.name from information_schema.innodb_sys_tables t, information_schema.innodb_sys_indexes i where t.name LIKE 'test%' AND i.name LIKE '%_IND' ORDER BY i.name;
+----------+-------------+---------+
| table_id | name        | name    |
+----------+-------------+---------+
|     3771 | test/child  | FOR_IND |
|     3770 | test/parent | FOR_IND |
|     3771 | test/child  | ID_IND  |
|     3770 | test/parent | ID_IND  |
|     3771 | test/child  | ID_IND  |
|     3770 | test/parent | ID_IND  |
|     3771 | test/child  | REF_IND |
|     3770 | test/parent | REF_IND |
+----------+-------------+---------+
8 rows in set (0.00 sec)

Posted by developer:
 
The "Using FOREIGN KEY" Constraints section explains that a name for a constraint is automatically generated if not provided:

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
If the CONSTRAINT symbol clause is given, the symbol value, if used, must be unique in the database. A duplicate symbol will result in an error similar to: ERROR 1022 (2300): Can't write; duplicate key in table '#sql- 464_1'. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, a name for the constraint is created automatically.

Regarding foreign key index naming:

0) If there is an explicitly defined index which can support FK, which is
   being created, its name will be used. Implicit supporting index won't be
   created in this case. verified.

1) If there is no explicit supporting index for FK, implicit index will
   be created. Its name is determined using the following rules:

   a) If CONSTRAINT <symbol> clause is present in FK definition <ident> will
      be used as name of implicit index.
   b) Otherwise name from FOREIGN KEY <index_name> clause will be used.
   c) If there is no CONSTRAINT clause and FOREIGN KEY clause doesn't
      specify foreign key name, the name will be auto-generated from
      referencing column names (as it happens for ordinary KEYs).

Posted by developer:
 
The FOREIGN KEY index_name option description has been revised to full explain foreign key index naming. 

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

Thank you for the bug report.