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
Tags: foreign key, information_schema, innodb_sys_indexes

[15 Mar 2014 16:20] Valeriy Kravchuk
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.
[17 Mar 2014 6:41] Umesh Shastry
Hello Valeriy,

Thank you for the report.

Thanks,
Umesh
[23 Oct 2014 14:59] Daniel Price
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)
[23 Oct 2014 21:28] Daniel Price
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.
[29 Oct 2014 0:56] Daniel Price
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).
[29 Oct 2014 12:00] Daniel Price
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.