This won't create a new index when the constraint is definedĪLTER TABLE main ADD FOREIGN KEY (language_id) REFERENCES main_language (id) Īccording to this note in the above paragraph: Already has a composite index with the FK column listed firstĪLTER TABLE main ADD INDEX (language_id, other_column)

So because the indexed columns are used left-to-right, if you already had this index on the column, creating the FOREIGN KEY constraint would not need to create a new index. index_name, if given, is used as described previously. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. Such an index is created on the referencing table automatically if it does not exist. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. ALTER TABLE main ADD FOREIGN KEY (language_id) REFERENCES main_language (id) ĪLTER TABLE main ADD INDEX (language_id) ĪLTER TABLE main ADD INDEX (other_column, language_id) Īccording to MySQL docs, MySQL will not create a new index on the FOREIGN KEY column if one is already present at the time the FOREIGN KEY is created. If you needed to add a different composite index which incorporated other columns, it would not be redundant but the single column is. Adding the FOREIGN KEY constraint implicitly creates an index on language_id.

Add foreign key mysql