Will FK validation in SQL Server always use the index specified in sys.foreign_keys?

Question

I cannot find anything to discuss when there are several possible indexes that can be used to back up the FK constraint.

As you can see from the test below, when creating an FK, the FK is bound to a specific index, and this will always be used to check the FK constraint, whether the new best index is added later.

Can anyone point out any resources confirming or denying this?

CREATE TABLE T1( T1_Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, Filler CHAR(4000) NULL, ) INSERT INTO T1 VALUES (''); CREATE TABLE T2( T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id), Filler CHAR(4000) NULL, ) /*Execution Plan uses clustered index - There is no NCI*/ INSERT INTO T2 VALUES (1,1) ALTER TABLE T1 ADD CONSTRAINT UQ_T1 UNIQUE NONCLUSTERED(T1_Id) /*Execution Plan still use clustered index even after NCI created*/ INSERT INTO T2 VALUES (1,1) SELECT fk.name, ix.name, ix.type_desc FROM sys.foreign_keys fk JOIN sys.indexes ix ON ix.object_id = fk.referenced_object_id AND ix.index_id = fk.key_index_id WHERE fk.name = 'FK' ALTER TABLE T2 DROP CONSTRAINT FK ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK FOREIGN KEY(T1_Id) REFERENCES T1 (T1_Id) /*Now Execution Plan now uses non clustered index*/ INSERT INTO T2 VALUES (1,1) SELECT fk.name, ix.name, ix.type_desc FROM sys.foreign_keys fk JOIN sys.indexes ix ON ix.object_id = fk.referenced_object_id AND ix.index_id = fk.key_index_id WHERE fk.name = 'FK' DROP TABLE T2 DROP TABLE T1 

Answer 1

Martin, I apologize that this is not a very answer, but since you were also curious about me, I did something. The information I can provide is as follows:

In current versions, including Denali, it is unlikely that an alternative will be considered in this situation.