MySQL Bugs: #10992: MyISAM foreign key definitions create indexes

Bug #10992	MyISAM foreign key definitions create indexes
Submitted:	31 May 2005 16:17	Modified:	28 Feb 2007 22:24
Reporter:	Dean Ellis	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	4.1.13, 5.1.52	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When foreign keys are defined without matching indexes being present (InnoDB's requirement), indexes are automatically created from 4.1 forward.  This is done even for other storage engines, which are supposed to ignore the FK definition entirely.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a INT, FOREIGN KEY (a) REFERENCES t1(a) ) ENGINE=MyISAM;
SHOW CREATE TABLE t1;

Suggested fix:
Do not automatically create indexes if the foreign key definition is to be ignored.

Affects other engines too.

mysql> create table bar (id int auto_increment not null primary key, foo_id int, constraint fk_foo_id foreign key (foo_id) references foo(id)) engine ndb; 
Query OK, 0 rows affected (1.66 sec)

mysql> show create table bar\G
*************************** 1. row ***************************
       Table: bar
Create Table: CREATE TABLE `bar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foo_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_foo_id` (`foo_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

5.1.52 is still affected:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.52-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( a INT, FOREIGN KEY (a) REFERENCES t1(a) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.24 sec)

mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Still in 8.0.3.