SQL Server外键约束冲突场景下批量删除关联表数据方案咨询

1. 查询数据库所有外键约束 #

运行以下SQL即可获取当前数据库所有外键的配置信息：

SELECT 
    fk.name AS 外键约束名称,
    OBJECT_NAME(fk.parent_object_id) AS 子表名称,
    c1.name AS 子表关联列,
    OBJECT_NAME(fk.referenced_object_id) AS 父表名称,
    c2.name AS 父表关联列,
    CASE WHEN delete_referential_action = 0 THEN 'Blocking(无操作)' 
         WHEN delete_referential_action = 1 THEN 'Cascade(级联删除)' END AS 删除规则
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN 
    sys.columns c1 ON fkc.parent_object_id = c1.object_id AND fkc.parent_column_id = c1.column_id
INNER JOIN 
    sys.columns c2 ON fkc.referenced_object_id = c2.object_id AND fkc.referenced_column_id = c2.column_id
ORDER BY 
    父表名称, 子表名称;

2. 修改外键约束删除规则 #

SQL Server不支持直接修改现有外键的删除规则，需要先删除原有约束、再重建对应规则的外键：

2.1 调整为Cascade级联删除规则 #

-- 删除原有外键约束
ALTER TABLE [子表名] DROP CONSTRAINT [外键约束名称];
-- 重建带级联删除规则的外键
ALTER TABLE [子表名] ADD CONSTRAINT [外键约束名称] 
    FOREIGN KEY ([子表关联列]) REFERENCES [父表名]([父表关联列])
    ON DELETE CASCADE;

2.2 恢复为Blocking默认规则 #

-- 删除现有外键约束
ALTER TABLE [子表名] DROP CONSTRAINT [外键约束名称];
-- 重建默认无操作规则的外键
ALTER TABLE [子表名] ADD CONSTRAINT [外键约束名称]
    FOREIGN KEY ([子表关联列]) REFERENCES [父表名]([父表关联列])
    ON DELETE NO ACTION;

3. 批量操作与批处理脚本 #

3.1 批量生成修改所有外键为级联删除的语句 #

运行以下SQL可直接生成全库外键改级联删除的执行脚本，复制输出结果即可批量执行：

SELECT 
    'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + '; ' + CHAR(13) +
    'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' + STRING_AGG(QUOTENAME(c1.name), ',') + ') REFERENCES ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + '(' + STRING_AGG(QUOTENAME(c2.name), ',') + ') ON DELETE CASCADE;' AS 批量修改语句
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN 
    sys.columns c1 ON fkc.parent_object_id = c1.object_id AND fkc.parent_column_id = c1.column_id
INNER JOIN 
    sys.columns c2 ON fkc.referenced_object_id = c2.object_id AND fkc.referenced_column_id = c2.column_id
GROUP BY 
    fk.object_id, fk.name, fk.parent_object_id, fk.referenced_object_id;

3.2 批量生成恢复所有外键为Blocking规则的语句 #

SELECT 
    'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + '; ' + CHAR(13) +
    'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' + STRING_AGG(QUOTENAME(c1.name), ',') + ') REFERENCES ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + '(' + STRING_AGG(QUOTENAME(c2.name), ',') + ') ON DELETE NO ACTION;' AS 批量恢复语句
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN 
    sys.columns c1 ON fkc.parent_object_id = c1.object_id AND fkc.parent_column_id = c1.column_id
INNER JOIN 
    sys.columns c2 ON fkc.referenced_object_id = c2.object_id AND fkc.referenced_column_id = c2.column_id
GROUP BY 
    fk.object_id, fk.name, fk.parent_object_id, fk.referenced_object_id;

3.3 批处理脚本示例 #

使用SQL Server自带的sqlcmd工具实现自动化执行，可直接写入.bat批处理文件：

@echo off
:: 替换为实际的数据库信息
set "server=SQL实例地址"
set "db=你的数据库名称"
set "user=登录账号"
set "pwd=登录密码"

:: 1.执行修改为级联删除的脚本
sqlcmd -S %server% -d %db% -U %user% -P %pwd% -i "C:\脚本路径\enable_cascade.sql"
:: 2.执行删除目标记录的脚本
sqlcmd -S %server% -d %db% -U %user% -P %pwd% -i "C:\脚本路径\delete_target.sql"
:: 3.执行恢复默认外键规则的脚本
sqlcmd -S %server% -d %db% -U %user% -P %pwd% -i "C:\脚本路径\disable_cascade.sql"

如果是Windows身份验证登录，将参数-U %user% -P %pwd%替换为-E即可。

注意：级联删除操作风险极高，执行前务必对全库做完整备份，确认删除范围不会误删其他必要数据，建议先在测试环境验证操作流程后再在生产环境执行。

内容的提问来源于stack exchange，提问作者Dominique