| Bug #78007 | Unnecessary index demand during Foreign Key creation | ||
|---|---|---|---|
| Submitted: | 10 Aug 2015 9:05 | Modified: | 11 Aug 2015 8:34 |
| Reporter: | Pavel Katiushyn | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.16 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[11 Aug 2015 8:00]
MySQL Verification Team
Hello Pavel, Thank you for the report. Imho this is known and documented behavior which is explained here http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html "MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously. InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order." // with index mysql> CREATE TABLE `parent` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `c1` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> alter table parent add key (id,c1); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE `child` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `parent_id` int(11) DEFAULT NULL, -> `c1` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`, `c1`) REFERENCES `parent` (`id`, `c1`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.03 sec) mysql> show create table child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int(11) NOT NULL DEFAULT '0', `parent_id` int(11) DEFAULT NULL, `c1` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `child_ibfk_1` (`parent_id`,`c1`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`, `c1`) REFERENCES `parent` (`id`, `c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Thanks, Umesh
[11 Aug 2015 8:34]
Pavel Katiushyn
Hi Umesh, Thank you for prompt reply. I think it may be improvement not to demand additional index in that case (especially for big tables). There is PK, that is enough for FK and there is no need to demand additional index.
Description: If foreign key is based on several columns and the first column belong to primary key, InnoDB still demands index based on all columns of foreign key. How to repeat: mysql> CREATE TABLE `parent` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `c1` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `child` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `parent_id` int(11) DEFAULT NULL, -> `c1` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`, `c1`) REFERENCES `parent` (`id`, `c1`) -> ) ENGINE=InnoDB; ERROR 1215 (HY000): Cannot add foreign key constraint mysql> alter table parent add key (id,c1); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE `child` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `parent_id` int(11) DEFAULT NULL, -> `c1` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`, `c1`) REFERENCES `parent` (`id`, `c1`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec)