如何在 SQL Server 中使用 T-SQL 禁用与重新启用外键约束

介绍在 SQL Server 中使用 T-SQL ALTER TABLE NOCHECK CONSTRAINT 禁用外键约束,通过 sys.foreign_keys 系统视图检查 is_disabled 与 is_not_trusted 状态,以及如何使用 WITH CHECK 安全重新启用外键。

发布于

在 SQL Server 中,外键(Foreign Key)用于强制维护表之间的引用完整性。

然而在进行大规模历史数据迁移、批量导入(Bulk Insert)或复杂的数据清理作业时,外键逐行检查可能会严重影响写入性能,甚至因为依赖顺序问题导致合法数据写入失败。为此,可以在维护期间临时禁用外键约束,并在操作完成后重新启用。

本文介绍如何使用 T-SQL 禁用外键约束、检查约束的信任状态,以及如何使用安全的方式重新启用外键。


示例 1:禁用外键约束

使用 ALTER TABLE ... NOCHECK CONSTRAINT 语句可以临时禁用指定的外键约束:

ALTER TABLE BandMember 
NOCHECK CONSTRAINT FK_BandMember_Musician;

执行后,往 BandMember 表插入或更新数据时,将不再对 Musician 表的外键引用进行实时一致性校验。


示例 2:查询系统视图验证约束状态

可以通过查询 sys.foreign_keys 目录视图检查外键约束的当前状态:

SELECT 
    name AS constraint_name,
    OBJECT_NAME(parent_object_id) AS table_name,
    OBJECT_NAME(referenced_object_id) AS referenced_table,
    is_disabled,
    is_not_trusted
FROM sys.foreign_keys;

输出:

constraint_name                 table_name        referenced_table  is_disabled  is_not_trusted
------------------------------  ----------------  ----------------  -----------  --------------
FK_BandMember_Band              BandMember        Band              0            0
FK_BandMember_Musician          BandMember        Musician          1            1
FK_MembershipPeriod_BandMember  MembershipPeriod  BandMember        0            0

从结果中可以观察到两个核心指标:

  • is_disabled = 1:表明该外键约束当前已处于禁用状态。
  • is_not_trusted = 1:表明该外键约束处于“不受信任”状态。因为约束被禁用过,SQL Server 无法保证在此期间写入的数据是否完全满足外键完整性。

示例 3:重新启用外键约束(关键:恢复信任)

批量数据处理完成后,必须尽快重新启用外键约束。

[!WARNING] 如果直接运行 ALTER TABLE BandMember CHECK CONSTRAINT FK_BandMember_Musician;,虽然启用了约束(is_disabled = 0),但系统不会扫描全表校验现有数据,此时 is_not_trusted 仍为 1。这会导致 SQL Server 查询优化器在生成执行计划时无法信任该外键,从而丧失连接消除(Foreign Key Join Elimination)等重要的性能优化!

最佳实践:使用 WITH CHECK 选项重新启用外键约束,让 SQL Server 立即扫描并校验所有现有行:

ALTER TABLE BandMember 
WITH CHECK CHECK CONSTRAINT FK_BandMember_Musician;

再次查询 sys.foreign_keys 状态:

SELECT 
    name AS constraint_name,
    is_disabled,
    is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_BandMember_Musician';

输出:

constraint_name         is_disabled  is_not_trusted
----------------------  -----------  --------------
FK_BandMember_Musician  0            0

可以看到,is_disabled 变为 0(已启用),且 is_not_trusted 变回 0(完全受信任),外键约束与查询优化器性能恢复到最佳状态。