Difference in FOREIGN KEY creation

Question

What is the difference in the following two CREATE TABLE statements? (The first one uses KEY and the second one does not.)

CREATE TABLE `title` (
    `title` VARCHAR(255) NOT NULL,
    `order_number` VARCHAR(35) NOT NULL,
    KEY `order_number` (`order_number`),
    CONSTRAINT `order_number_fk` FOREIGN KEY (`order_number`)
        REFERENCES `order` (`order_number`) ON DELETE CASCADE
)

CREATE TABLE `title` (
    `title` VARCHAR(255) NOT NULL,
    `order_number` VARCHAR(35) NOT NULL,
    CONSTRAINT `order_number_fk` FOREIGN KEY (`order_number`)
        REFERENCES `order` (`order_number`) ON DELETE CASCADE
)

Both of them create valid tables. How are they different and which would I want to use?

Answer 1

They are (almost*) the same.

When you create a foreign key constraint, an index is created on the relevant column(s) of the referencing table automatically if no suitable index already exists.

From the manual page on FOREIGN KEY Constraints:

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 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.

Emphasis mine.

---

(*) I say almost the same because there a few subtle differences.

The name of the index

In the first version you have given the index an explicit name, but in the second version the name of the index is the same as the name of the constraint (if it is specified).

Compare the output of SHOW INDEX in both cases:

Version 1:

Table  Non_unique  Key_name          Seq_in_index   Column_name   ...
title  1           order_number      1              order_number  ...

Version 2:

Table  Non_unique  Key_name          Seq_in_index   Column_name   ...
title  1           order_number_fk   1              order_number  ...

As you can see, the only difference here is the name of the index.

Silent dropping

Another subtle difference is that in the second case, as the documentation mentions, the automatically created index could be silently dropped when new indexes are added:

This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint.

This means is that if you later create a multicolumn index on, for example, (order_number, title):

CREATE INDEX ix_order_number_title ON title (order_number, title);

Then run SHOW INDEX again:

Version 1:

Table  Non_unique  Key_name               Seq_in_index   Column_name   ...
title  1           order_number           1              order_number  ...
title  1           ix_order_number_title  1              order_number  ...
title  1           ix_order_number_title  2              title         ...

Version 2:

Table  Non_unique  Key_name               Seq_in_index   Column_name   ...
title  1           ix_order_number_title  1              order_number  ...
title  1           ix_order_number_title  2              title         ...

Now you can see that the first version has two indexes but the second version has only one. With the second version, the index that was automatically created by the foreign key constraint was automatically dropped again when the multi-column index was added. Normally this isn't a serious issue because the new index makes the original index mostly redundant.

Which would I want to use?

Normally you don't need to worry about explicitly creating the index on the referencing table of a foreign key constraint.

But you might want to create an index explicitly if:

• you prefer to give it a name that is different from the name of the constraint, or
• you don't want the index to silently disappear when other indexes are added.