I have a table named MyTable
. The primary key is an identity int column named MyTableID
. There is a unique clustered index on the PK column MyTableID
named PK_MyTable
.
I noticed there is an additional non-clustered unique index IX_MyTable_MytableID
on that table with a single column MyTableID
, and no other included columns. This index is obviously redundant, but when I try to delete it, I get an error message:
The constraint 'IX_MyTable_MyTableID' is being referenced by table 'OtherTable',
foreign key constraint 'FK__OtherTable__MyTableID__369C23FC'.
Why is the FK constraint relying on the non-clustered unique index instead of the primary key constraint? How do I update the FKs to use the clustered PK index instead of the other index?
Best Answer
Because a foreign key can point to a primary key or a unique constraint, and whoever created that foreign key possibly created it before the primary key existed (or they shifted the FK to point to the Unique index while they changed something else about the primary key). This is easy to repro:
In fact, both of these foreign keys will point to the first unique constraint defined on that column (
myx
).You can fix the foreign key on the other table by dropping it and re-creating it. You will need to repeat that process for any other tables that point to this column. You can find these easily:
Results:
And even generate a script to drop and re-create them (dropping the redundant unique constraint in the meantime):
Results:
This explicitly handles this case, where the constraint only involves a single column. It gets a little more complex if there are multiple columns involved (and this answer is not meant to solve that problem). I also didn't test if this works exactly as coded if the foreign keys point to a redundant unique index (which has the same underlying structure but is created with slightly different DDL). Exercise for the reader. :-)