Unique constraint replaces existing foreign key

All we need is an easy explanation of the problem, so here it is.

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.


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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply