MySQL foreign key question

Question

Does defining a foreign key also defines a index? I have mysql v5.1.46 & I am looking at the MySQL Administrator tool and its shows the foreign key as an index, so I wanted to confirm?

Answer 1

Yes, MySQL 5.1 automatically creates an index on the referencing table when you define a foreign key constraint. MySQL requires an index on both the referencing table and referenced table for foreign keys.

Note however that the index is created automatically only on the referencing table, and not on the referenced table. MySQL won't allow you to create a foreign key that references a field in the referenced table that cannot use an index:

CREATE TABLE orders (
  id int PRIMARY KEY,
  code int,
  name varchar(10)
) ENGINE=INNODB;

CREATE TABLE order_details (
  detail_id int PRIMARY KEY,
  order_code int,
  value int,
  FOREIGN KEY (order_code) REFERENCES orders(code)
) ENGINE=INNODB;

ERROR 1005 (HY000): Can't create table 'test.order_details'

This is not very common, since you'd often be creating foreign key constraints that reference the primary key of the referenced table, and primary keys are indexed automatically. However it is probably worth keeping in mind.

Creating an index on the code field of the orders table would solve the problem:

CREATE INDEX ix_orders_code ON orders(code);

Answer 2

• If there already is a usable index (an index where the foreign key columns are listed as the first columns in the same order) then a new index is not created.
• If there is no usable index then creating a foreign key also creates an index.

This is covered in the documentation.

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.