Unique constraint replaces existing foreign key

Question

I have two tables, parent and child, the child table has a FK on the parent table using the parent_id column. When adding this FK I can see that the child table has both the FK and an index to represent that FK.

But when I add a unique constraint on the child table that contains parent_id then I can see that the index representing the FK is replaced with the new unique constraint.

Ex:

create table parent
(
    parent_id int unsigned not null auto_increment primary key
);
create table child
(
    child_id int unsigned not null auto_increment primary key,
    parent_id int unsigned not null,
    age smallint unsigned null,
    constraint FK__CHILD__PARENT
        foreign key (parent_id) references parent (parent_id)
);

At this point I can see that both an FK and an index called FK_CHILD_PARENT exist on the child table.

Now I add the unique constraint:

alter table child add constraint unique_parentId_age unique (parent_id, age);

I can see that the unique_parentId_age index replaced the FK__CHILD__PARENT index.

I don't understand why this is happening? Is it impossible to have an index representing a FK if another index already exists which starts with the same column as the FK.

I'm using MySQL 5.7

Answer 1

Is it impossible to have an index representing a FK if another index already exists which starts with the same column as the FK.

An index does not represent the foreign key constraint; it exists independently from the constraint and supports its enforcement. It is technically possible to have a redundant index but it's unnecessary: any query that benefits from an index on (parent_id) can be supported equally well by an index on (parent_id, age), and maintaining redundant indexes is detrimental to performance.

This is working as designed and documented (emphasis mine):

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.

If for some reason you insist on having a redundant index, I guess you could try to create it explicitly and give it a non-default name.