MySQL Bugs: #41678: SHOW KEYS does not display all foreign keys

Bug #41678	SHOW KEYS does not display all foreign keys
Submitted:	22 Dec 2008 16:22	Modified:	23 Dec 2008 21:51
Reporter:	Martin Below	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.51b	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Consider the schema below: The table foo2bar has two foreign key constraints, and a primary key which is composed of the two foreign keys.

This is what SHOW KEYS does display:
bar_id (primary)
foo_id (primary)
foo_id (fk_foo_id)

Notice the missing fk_bar_id constraint.

If the order of the primary constraint is changed in the ddl to "bar_id, foo_id", the equivalent happens with the fk_bar_id not beeing displayed.

How to repeat:
create database foobar;
use foobar;

create table foo (
	id int(4) unsigned not null,
	primary key (id)
) engine=InnoDB;

create table bar (
	id int(4) unsigned not null,
	primary key (id)
) engine=InnoDB;

create table foo_bar (
	bar_id int(4) unsigned not null,
	foo_id int(4) unsigned not null,
	primary key (bar_id, foo_id),
	foreign key fk_bar_id (bar_id) references bar(id),
	foreign key fk_foo_id (foo_id) references foo(id)

) engine=InnoDB;

SHOW KEYS FROM foo_bar;

Indeed, this is what one can get on 5.0.74, 5.1.30 and 6.0.8:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.0.8-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table foo (
    ->  id int(4) unsigned not null,
    ->  primary key (id)
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> create table bar (
    ->  id int(4) unsigned not null,
    ->  primary key (id)
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> create table foo_bar (
    ->  bar_id int(4) unsigned not null,
    ->  foo_id int(4) unsigned not null,
    ->  primary key (bar_id, foo_id),
    ->  foreign key fk_bar_id (bar_id) references bar(id),
    ->  foreign key fk_foo_id (foo_id) references foo(id)
    ->
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.17 sec)

mysql> SHOW KEYS FROM foo_bar\G
*************************** 1. row ***************************
        Table: foo_bar
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: bar_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_Comment:
*************************** 2. row ***************************
        Table: foo_bar
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 2
  Column_name: foo_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_Comment:
*************************** 3. row ***************************
        Table: foo_bar
   Non_unique: 1
     Key_name: fk_foo_id
 Seq_in_index: 1
  Column_name: foo_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_Comment:
3 rows in set (0.00 sec)

But this is not a bug. The table is created like this:

mysql> show create table foo_bar\G
*************************** 1. row ***************************
       Table: foo_bar
Create Table: CREATE TABLE `foo_bar` (
  `bar_id` int(4) unsigned NOT NULL,
  `foo_id` int(4) unsigned NOT NULL,
  PRIMARY KEY (`bar_id`,`foo_id`),
  KEY `fk_foo_id` (`foo_id`),
  CONSTRAINT `foo_bar_ibfk_1` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`),
  CONSTRAINT `foo_bar_ibfk_2` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

There is no need for a separate key on bar_id, as it is the first column of a primary key. Read the manual, http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html:

"- InnoDB 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 is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

- InnoDB allows 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."

PRIMARY KEY is good for both purposes as index for `foo_bar_ibfk_1` constraint, so there is no need to create any other indexes.

Thanks for the reply! I didn't knew that SHOW KEYS is a synonym for SHOW INDEX, and was expecting that SHOW KEYS would give me all keys of the table. I still think that this would make sense btw.

cheeers,
Martin