为具有ON CASCADE约束的记录预览SQL DELETE

We have a master 'users' table and many tables that refer to the UserId either

我们有一个主'用户'表和许多引用UserId的表

Directly: UserId is a FK in the child table OR
直接：UserId是子表OR中的FK
Indirectly: another FK in the 'grandchild' table that references a record in the child table, that in turn refers to UserID via a FK constraint.
间接地：'grandchild'表中的另一个FK引用子表中的记录，而该表又通过FK约束引用UserID。

Now if everything is perfect, deleting a user should be as straightforward as deleting them in the master table and the ON CASCADE constraints rippling it thru the rest of the tables. The issue is that we're not 100% sure if every FK relationship in every table referenced (directly or indirectly) has the ON CASCADE constraint. We need some way to issue that delete and watch which tables SQL server actually touches to delete. I read this and tried it but it doesn't display any tables cascaded into - just the entries in the master table only

现在，如果一切都很完美，删除用户应该像在主表中删除它们一样简单，并且ON CASCADE约束通过其余表格将其波动。问题是我们并不是100％确定引用的每个表中的每个FK关系（直接或间接）是否具有ON CASCADE约束。我们需要一些方法来发出删除并观察SQL Server实际触摸要删除的表。我读了这个并尝试了但它没有显示任何级联的表 - 只是主表中的条目

Here is what I tried:

这是我尝试过的：

DELETE umt
OUTPUT DELETED.*
FROM [OurAppDb].[dbo].[UserMasterTable] umt
WHERE umt.UserId LIKE 'ABCDABCD-ABCD-ABCD-ABCD-ABCDABCDABCD'

How can I see all the tables the above query would touch?

如何查看上述查询将触及的所有表格？

NOTE: The ON CASCADE constraint is a constraint in the database that we think we added for every table when each table was built. Example of it being added on one table

注意：ON CASCADE约束是我们认为在构建每个表时为每个表添加的数据库中的约束。它被添加到一个表上的示例

ALTER TABLE [dbo].[UserEmailPrefs]  
WITH CHECK ADD  CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserMasterTable] ([UserId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UserEmailPrefs] CHECK CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId]
GO

2 个解决方案

#1

To inspect the referential constraints in your entire database that reference the UserMasterTable, use INFORMATION_SCHEMA views.

要检查引用UserMasterTable的整个数据库中的引用约束，请使用INFORMATION_SCHEMA视图。

SELECT RC.CONSTRAINT_NAME, TU.TABLE_NAME, RC.DELETE_RULE, RC.UPDATE_RULE 
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
  INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE TU
          ON RC.CONSTRAINT_CATALOG = TU.CONSTRAINT_CATALOG 
         AND RC.CONSTRAINT_NAME = TU.CONSTRAINT_NAME
  INNER JOIN INFORMATION_SCHEMA.table_constraints TC 
          ON RC.unique_constraint_name = TC.CONSTRAINT_NAME
  WHERE TC.TABLE_NAME='Users'

This returns a list of the referential constraints targeting UserMasterTable, and for each one, which table is referencing UserMasterTable, and what the ON DELETE and ON UPDATE rules are. From this you can quickly see which referential constraints are lacking the desired CASCADE rule. No need to get trigger-happy.

这将返回一个针对UserMasterTable的引用约束列表，并为每个引用约束，哪个表引用UserMasterTable，以及ON DELETE和ON UPDATE规则是什么。通过此，您可以快速查看哪些参照约束缺少所需的CASCADE规则。无需触发快乐。

To extend this to "grandchild" references, add two more join clauses. To extend it to any number of levels, go for a recursive CTE.

要将其扩展为“孙子”引用，请再添加两个join子句。要将其扩展到任意数量的级别，请进行递归CTE。

#2

I think you can do this in trigger(analyse [inserted] table and search for subordinated tables). As example: you can create table which will store queries for detecting Foreign Key links :

我认为你可以在触发器中执行此操作（分析[inserted]表并搜索从属表）。例如：您可以创建用于存储查询以检测外键链接的表：

IF NOT EXISTS (
        SELECT *
        FROM [sys].tables T
        WHERE T.NAME = 'FKCheck'
        )
    CREATE TABLE FKCheck (
        TableName SYSNAME
        ,ChildTable SYSNAME
        ,QueryText NVARCHAR(MAX)
        )
ELSE
    EXEC('DROP TABLE FKCheck')

Then fill it with dynamic query extracted from metadata

然后用从元数据中提取的动态查询填充它

;WITH CTE_FKs
AS (
    SELECT FK.NAME
        ,OBJECT_name(fk.parent_object_id) AS ChildTable
        ,OBJECT_name(fk.referenced_object_id) AS ParentTable
        ,delete_referential_action_desc AS DeleteAction
        ,MainTable.NAME AS MainTableColumn
        ,ChildObject.NAME AS ChildColumnName
    FROM sys.foreign_keys FK
    INNER JOIN sys.foreign_key_columns FKC ON FKC.constraint_object_id = FK.object_id
    INNER JOIN sys.columns AS ChildObject ON ChildObject.object_id = FKc.parent_object_id
        AND FKC.parent_column_id = ChildObject.column_id
    INNER JOIN sys.columns AS MainTable ON MainTable.object_id = FK.referenced_object_id
        AND MainTable.column_id = FKC.referenced_column_id
    )
    ,CTE_Tables
AS (
    SELECT DISTINCT C.NAME
        ,C.ParentTable
        ,C.DeleteAction
        ,C.ChildTable
    FROM [CTE_FKs] C
    )
INSERT INTO [dbo].[FKCheck] (
    TableName
    ,ChildTable
    ,QueryText

    )
SELECT C.ParentTable,C.ChildTable
    ,'IF EXISTS (select 1 from inserted INNER JOIN  ' + QUOTENAME(C.ChildTable) + ' ON ' + STUFF((
            SELECT ' AND inserted.' + QUOTENAME(C2.MainTableColumn) + ' = ' + + QUOTENAME(C2.ChildTable) + '.' + QUOTENAME(C2.ChildColumnName)
            FROM CTE_FKs C2
            WHERE C2.ParentTable = C.ParentTable
                AND C2.NAME = C.NAME
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'nvarchar(MAX)'), 1, 4, '') + ')
 RAISERROR(''Relation with ' + QUOTENAME(C.ChildTable) +':'+ CASE C.DeleteAction
        WHEN 'CASCADE'
            THEN ' data will be deleted'
        WHEN 'SET_NULL'
            THEN ' set as NULL'
        WHEN 'NO_ACTION'
            THEN ' no default action'
        ELSE 'Unknown'
        END + ''')'
FROM [CTE_Tables] C

Your query in table will be looking like:

您在表中的查询将如下所示：

 IF EXISTS (select 1 from inserted INNER JOIN  [UserEmailPrefs] ON  inserted.[UserId] = [UserEmailPrefs].[UserId])
 RAISERROR('Relation with [UserEmailPrefs]: no default action')
IF EXISTS (select 1 from inserted INNER JOIN  [UserEmail] ON  inserted.[UserId] = [UserEmail].[UserId])
 RAISERROR('Relation with [UserEmail]: set as NULL')

Then in trigger you can execute query to print message:

然后在触发器中，您可以执行查询以打印消息：

 DECLARE @TableName SYSNAME = 'UserMasterTable';
    DECLARE @sSQL NVARCHAR(MAX) = '';

    SELECT @sSQL += F.QueryText + CHAR(10)
    FROM FKCheck F
    WHERE F.TableName = @TableName;
EXEC(@sSQL)
ROLLBACK

If you need to analyze more "distant" tables, you need to walk through hierarchy in FKCheck table.

如果需要分析更多“远程”表，则需要在FKCheck表中遍历层次结构。

#1