All we need is an easy explanation of the problem, so here it is.
I have two tables,
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
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
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.
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 🙂