Why MySQL creating indexes for Foreign key during table updation

Question

It is given in documentation that when you add a foreign key using alter table command then MySQL does not create an index for foreign key and you need to create it manually.

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

But I added a foreign-key in the table using alter table command and it is creating index along with constraint.

ALTER TABLE `pharmaceuticals_dosage` ADD CONSTRAINT `pharmaceuticals_dosa_as_needed_reason_cod_2ac978bf_fk_human_api` FOREIGN KEY (`as_needed_reason_code_id`) REFERENCES `human_api_code` (`id`);

I assume that MySQL official documentation should not be wrong, So any reason why it is creating index?

PS: Above Query is created by Django migration

MySQL adds index in child, if another index where referential field is a prefix not exisis. *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* But it didn't create the index in parent, even if it is self-reference FK. — Akina, Dec 26 '19 at 16:35

score 1 · Accepted Answer · answered Dec 26 '19 at 18:30

The documentation to which you refer is correct, but not clear enough.

Suppose you had a table of employees. You want each row to have a column manager_id which is a foreign key referencing another row which is the employee's manager. But for some reason, you don't use employee_id as the primary key of the table -- you have another primary key.

mysql> create table employees (
  id serial primary key, 
  employee_id int, 
  manager_id int
);

You would like to add a foreign key on manager_id, referencing employee_id:

mysql> alter table employees add foreign key (manager_id) references employees(employee_id);
ERROR 1215 (HY000): Cannot add foreign key constraint

What's wrong? There's no index on employee_id but a foreign key can only reference a column with an index. This is a little more clear if we get the detail about the foreign key error in the innodb status:

mysql> show engine innodb status\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-12-26 10:25:52 0x700005bfb000 Error in foreign key constraint of table test/#sql-159d_8c9:
foreign key (manager_id) references employees(employee_id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns...
...

You need to create an index on employee_id first:

mysql> alter table employees add unique key (employee_id);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table employees add foreign key (manager_id) references employees(employee_id);
Query OK, 0 rows affected (0.02 sec)

So the documentation was correct to say "remember to create the required indexes first," but it was not clear that the required index is on the referenced column (employee_id). You assumed the documentation was talking about a required index on the foreign key column (manager_id).

In fact, creating the foreign key will automatically create the index on the foreign key column, unless an index on that column already exists.

I've logged a bug to improve the MySQL doc: https://bugs.mysql.com/bug.php?id=98078 — Bill Karwin, Dec 26 '19 at 18:43
Thanks Bill, Can you please look into https://stackoverflow.com/questions/59490756/mysql-not-creating-indexes-for-foreign-key too. — SHIVAM JINDAL, Dec 27 '19 at 12:30

score 0 · Answer 2 · answered Dec 26 '19 at 16:39

Since you have created the foreignkey using django, it will create database index automatically. It is mentioned in the Documentation of the ForeignKey. From the Docs:

A database index is automatically created on the ForeignKey.

You can disable the foreignkey by adding db_index=False to that field. For eg.

class SomeModel(models.Model):
    refrence_to_table = models.ForeignKey(SomeTable, db_index=False)
    # other fields

Attaching the text from Docs:

A database index is automatically created on the ForeignKey. You can disable this by setting db_index to False. You may want to avoid the overhead of an index if you are creating a foreign key for consistency rather than joins, or if you will be creating an alternative index like a partial or multiple column index.

I guess MySQL docs refers when you are directly creating a foreignkey (from shell) to other table. Here Django will generate a custom sql while create.

You can check the Schema used by Django in different DB operations.

I checked the query generated by Django using `sqlmigrate` and it is creating only `Alter table add foreign key` query. Can you please specify little more by which I can infer the query for creating index generated by Django. — SHIVAM JINDAL, Dec 26 '19 at 17:21

Why MySQL creating indexes for Foreign key during table updation

2 Answers2