SQL Server中根据delete_referential_action值动态生成带ON DELETE CASCADE的外键约束语句求助

我明白你的需求了——你想根据外键的delete_referential_action和update_referential_action值，自动在生成的ALTER TABLE语句里加上对应的ON DELETE CASCADE或ON UPDATE CASCADE。你的原查询问题出在把条件判断放错了位置，应该在拼接完REFERENCES子句之后，再动态添加这些CASCADE选项。

下面是修正后的完整SQL语句：

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N' ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' 
    + STUFF((
        SELECT ',' + QUOTENAME(c.name) 
        FROM sys.columns AS c 
        INNER JOIN sys.foreign_key_columns AS fkc 
            ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.[object_id] 
        WHERE fkc.constraint_object_id = fk.[object_id] 
        ORDER BY fkc.constraint_column_id 
        FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') 
    + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name) + '(' 
    + STUFF((
        SELECT ',' + QUOTENAME(c.name) 
        FROM sys.columns AS c 
        INNER JOIN sys.foreign_key_columns AS fkc 
            ON fkc.referenced_column_id = c.column_id AND fkc.referenced_object_id = c.[object_id] 
        WHERE fkc.constraint_object_id = fk.[object_id] 
        ORDER BY fkc.constraint_column_id 
        FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') 
    + ')'
    -- 动态添加ON DELETE CASCADE
    + CASE WHEN fk.delete_referential_action = 1 THEN N' ON DELETE CASCADE' ELSE N'' END
    -- 动态添加ON UPDATE CASCADE
    + CASE WHEN fk.update_referential_action = 1 THEN N' ON UPDATE CASCADE' ELSE N'' END
    + N';'
FROM sys.foreign_keys AS fk 
INNER JOIN sys.tables AS rt -- referenced table
    ON fk.referenced_object_id = rt.[object_id] 
INNER JOIN sys.schemas AS rs 
    ON rt.[schema_id] = rs.[schema_id] 
INNER JOIN sys.tables AS ct -- constraint table
    ON fk.parent_object_id = ct.[object_id] 
INNER JOIN sys.schemas AS cs 
    ON ct.[schema_id] = cs.[schema_id] 
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

-- 查看生成的SQL语句
PRINT @create;

关键修正点： #

• 把条件判断移到了REFERENCES子句的闭合括号之后，使用CASE语句来判断是否需要添加CASCADE选项
• 分别处理delete_referential_action和update_referential_action两个字段，各自生成对应的ON子句
• 确保了SQL语句的语法正确性，不会因为条件判断导致结构混乱

执行这个查询后，@create变量里就会生成符合你需求的ALTER TABLE语句：当delete_referential_action=1时自动加上ON DELETE CASCADE，update_referential_action=1时自动加上ON UPDATE CASCADE，两个值都为1时则同时添加两个选项。

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