代码之家 › 专栏 › 技术社区 › Malick

删除多个表外键上的级联时删除

tsql sql-server

Malick · 技术社区 · 8 年前

我有几个表具有外键约束,可以选择 ON DELETE CASCADE . 每个表都属于同一个名为 datasets .

我可以使用以下方法检索完整的表列表:

SELECT * 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA ='datasets'

对于每个表,我要删除 关于删除级联 名为的外键约束上的选项 FK_[TABLENAME]_SerieID 在哪里? [TABLENAME] 对应于表的名称(和 SerieId 是相同的外键。

我能够对特定的表执行操作,例如 Table1 使用:

ALTER TABLE  datasets.Table1
    DROP CONSTRAINT FK_Table1_SerieID

ALTER TABLE datasets.Table1
    ADD CONSTRAINT FK_Table1_SerieID
        FOREIGN KEY (Serie_Id) REFERENCES[dbo].[Serie](SerieID)
        ON DELETE NO ACTION
GO

我要对属于架构的每个表执行上述操作 数据集 . 我对t-sql还不熟悉,不知道怎么做。

我应该用光标吗?你能帮我做这个吗?

我正在使用SQL Server 2016。

1 回复 | 直到 8 年前

Lukasz Szozda 8 年前

我不会重新发明轮子。有个很好的剧本是由 Aaron Bertrand: Drop and Re-Create All Foreign Key Constraints in SQL Server .

你可以很容易地扩展它来处理 NO ACTION 通过添加简单的 WHERE 限制:

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

-- drop is easy,just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE delete_referential_action_desc <> 'NO_ACTION'  -- here 
    AND cs.name = 'datasets';

-- create is a little more complex. We need to generate the list of 
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += N'
ALTER TABLE ' 
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the columns in the constraint table
    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)
   -- get all the referenced columns
    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'') + ');'
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
  AND delete_referential_action_desc <> 'NO_ACTION' -- here
  AND cs.name = 'datasets';
print(@drop);
print(@create);
-- ...

DBFiddle Demo

一个警告!请避免添加 ORDER BY .

此脚本使用

SELECT @drop += N'...'
<=>
SELECT @drop = @drop + N'...'

可能会产生不正确的结果。更多 nvarchar concatenation / index / nvarchar(max) inexplicable behavior