更改外鍵的引用索引

我有這樣的事情：

CREATE TABLE T1 (
   Id INT
   ...
   ,Constraint [PK_T1] PRIMARY KEY CLUSTERED [Id]
)

CREATE TABLE T2 (
   ....
   ,T1_Id INT NOT NULL
   ,CONSTRAINT [FK_T2_T1] FOREIGN KEY (T1_Id) REFERENCES T1(Id)
)

出於性能（和死鎖）的原因，我在 T1 上創建了一個新索引

CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)

但是，如果我檢查哪個索引引用了 FK，則繼續引用聚集索引

select
   ix.index_id,
   ix.name as index_name,
   ix.type_desc as index_type_desc,
   fk.name as fk_name
from sys.indexes ix
   left join sys.foreign_keys fk on
       fk.referenced_object_id = ix.object_id
       and fk.key_index_id = ix.index_id
       and fk.parent_object_id = object_id('T2')
where ix.object_id = object_id('T1');

如果我刪除約束並再次創建它會引用非聚集索引，但這會導致再次檢查所有 t2 FK。

有沒有辦法改變這一點，所以 FK_T2_T1 使用 IX_T1_Id 而不是 PK_T1 而不刪除 FK 並在 FK 檢查時鎖定表？

謝謝！

好吧，繼續搜尋後，我找到了這篇文章

與普通查詢不同，它不會因為更新統計資訊、創建新索引甚至重新啟動伺服器而獲取新索引。我知道將 FK 綁定到不同索引的唯一方法是刪除並重新創建 FK，讓它自動選擇索引，而沒有手動控制它的選項。

因此，除非有人另有說法，否則我將不得不尋找一個時間視窗來執行此任務。

謝謝

在這裡閱讀 MS DOCS 後。

修改外鍵

要使用 Transact-SQL 修改 FOREIGN KEY 約束，您必須首先刪除現有的 FOREIGN KEY 約束，然後使用新定義重新創建它。有關詳細資訊，請參閱刪除外鍵關係和創建外鍵關係。

在你的情況下，我相信添加一個新的 FK 並刪除舊的。要禁用掃描，您可以使用NO CHECK選項

--DROP TABLE T2
--DROP TABLE T1


CREATE TABLE T1 (
   [Id] INT,
   [NAME] varchar(100), CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED (id))

CREATE TABLE T2 (
   t2_id int,
   T1_Id INT NOT NULL
   ,CONSTRAINT [FK_T2_T1] FOREIGN KEY (T1_Id) REFERENCES T1(Id)
)


CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)


select
   ix.index_id,
   ix.name as index_name,
   ix.type_desc as index_type_desc,
   fk.name as fk_name
from sys.indexes ix
   left join sys.foreign_keys fk on
       fk.referenced_object_id = ix.object_id
       and fk.key_index_id = ix.index_id
       and fk.parent_object_id = object_id('T2')
where ix.object_id = object_id('T1');



╔══════════╦════════════╦═════════════════╦══════════╗
║ index_id ║ index_name ║ index_type_desc ║ fk_name  ║
╠══════════╬════════════╬═════════════════╬══════════╣
║        1 ║ PK_T1      ║ CLUSTERED       ║ FK_T2_T1 ║
║        2 ║ IX_T1_Id   ║ NONCLUSTERED    ║ NULL     ║
╚══════════╩════════════╩═════════════════╩══════════╝




ALTER TABLE T2
   WITH NOCHECK 
   ADD CONSTRAINT [FK_T2_T1_NEW] FOREIGN KEY(T1_Id)
   REFERENCES T1(Id)

select
   ix.index_id,
   ix.name as index_name,
   ix.type_desc as index_type_desc,
   fk.name as fk_name
from sys.indexes ix
   left join sys.foreign_keys fk on
       fk.referenced_object_id = ix.object_id
       and fk.key_index_id = ix.index_id
       and fk.parent_object_id = object_id('T2')
where ix.object_id = object_id('T1');


╔══════════╦════════════╦═════════════════╦══════════════╗
║ index_id ║ index_name ║ index_type_desc ║   fk_name    ║
╠══════════╬════════════╬═════════════════╬══════════════╣
║        1 ║ PK_T1      ║ CLUSTERED       ║ FK_T2_T1     ║
║        2 ║ IX_T1_Id   ║ NONCLUSTERED    ║ FK_T2_T1_NEW ║
╚══════════╩════════════╩═════════════════╩══════════════╝   

ALTER TABLE T2  
DROP CONSTRAINT FK_T2_T1 

select
   ix.index_id,
   ix.name as index_name,
   ix.type_desc as index_type_desc,
   fk.name as fk_name
from sys.indexes ix
   left join sys.foreign_keys fk on
       fk.referenced_object_id = ix.object_id
       and fk.key_index_id = ix.index_id
       and fk.parent_object_id = object_id('T2')
where ix.object_id = object_id('T1');


╔══════════╦════════════╦═════════════════╦══════════════╗
║ index_id ║ index_name ║ index_type_desc ║   fk_name    ║
╠══════════╬════════════╬═════════════════╬══════════════╣
║        1 ║ PK_T1      ║ CLUSTERED       ║ NULL         ║
║        2 ║ IX_T1_Id   ║ NONCLUSTERED    ║ FK_T2_T1_NEW ║
╚══════════╩════════════╩═════════════════╩══════════════╝

看看這是否可行，我正在嘗試再添加一個 FK，以便將新的 FK 連結到創建的新索引並刪除舊的 FK。我知道問題不是放棄現有的，而是看看這個選項是否會對你有所幫助。

此外，根據 Max Vernon 的評論：“ WITH NOCHECK 選項將阻止優化器信任外鍵。在某些時候，您必須更改外鍵以便使用 ALTER TABLE 來信任它……帶支票”

NOCHECK只會在創建時被忽略，但為了強制執行完整性約束，您已經在某個時間點執行它。

引用自：https://dba.stackexchange.com/questions/246381