主页 / dba / 问题 / 246381
Accepted
Mariano G
Asked: 2019-08-28 08:28:37 +0800 CST

更改外键的引用索引

  • 772

我有这样的事情：

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 检查时锁定表？

谢谢！

sql-server sql-server-2014

2 个回答

  1. Best Answer

    好吧，继续搜索后，我找到了这篇文章

    与普通查询不同，它不会因为更新统计信息、创建新索引甚至重新启动服务器而获取新索引。我知道将 FK 绑定到不同索引的唯一方法是删除并重新创建 FK，让它自动选择索引，而没有手动控制它的选项。

    因此，除非有人另有说法，否则我将不得不寻找一个时间窗口来执行此任务。

    谢谢

    • 6

  2. 在这里阅读 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只会在创建时被忽略，但为了强制执行完整性约束，您已经在某个时间点运行它。

    • 3

相关问题