Unique constraint replaces existing foreign key
I have two tables,
childtable has a FK on the
parenttable using the
parent_idcolumn. When adding this FK I can see that the
childtable has both the FK and an index to represent that FK.
But when I add a unique constraint on the
childtable that contains
parent_idthen 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_PARENTexist 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_ageindex 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
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 https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html#foreign-key-restrictions (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.