sql - SQL中的级联菱形删除

Question

如果我的数据库中有一个简单的 User 表和一个以 User.id 作为外键的简单 Item 表:

(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
name NVARCHAR (MAX) NULL,
email NVARCHAR (128) NULL,
authenticationId NVARCHAR (128) NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (id))

CREATE TABLE Items
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
userId UNIQUEIDENTIFIER NOT NULL,
name NVARCHAR (MAX) NULL,
description NVARCHAR (MAX) NULL,
isPublic BIT DEFAULT 0 NOT NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (userId) REFERENCES Users (id))

如果从表中删除用户，我需要先删除所有相关项以避免破坏引用完整性约束。这很容易用 CASCADE DELETE

完成

CREATE TABLE Items
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
userId UNIQUEIDENTIFIER NOT NULL,
name NVARCHAR (MAX) NULL,
description NVARCHAR (MAX) NULL,
isPublic BIT DEFAULT 0 NOT NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (userId) REFERENCES Users (id) ON DELETE CASCADE)

但如果我还有引用用户的集合，以及一个将项目收集到集合中的表，我就会遇到麻烦，即以下附加代码不起作用。

CREATE TABLE Collections
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
userId UNIQUEIDENTIFIER NOT NULL,
name NVARCHAR (MAX) NULL,
description NVARCHAR (MAX) NULL,
isPublic BIT DEFAULT 0 NOT NULL,
layoutSettings NVARCHAR (MAX) NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (userId) REFERENCES Users (id) ON DELETE CASCADE)

CREATE TABLE CollectedItems
(itemId UNIQUEIDENTIFIER NOT NULL,
collectionId  UNIQUEIDENTIFIER NOT NULL,
createdAt DATETIME DEFAULT GETDATE() NOT NULL,
PRIMARY KEY CLUSTERED (itemId, collectionId),
FOREIGN KEY (itemId) REFERENCES Items (id) ON DELETE CASCADE,
FOREIGN KEY (collectionId) REFERENCES Collections (id) ON DELETE CASCADE)

错误表明这“可能导致循环或多个级联路径”。我看到推荐的解决方法是

1. 重新设计表格，但我不知道怎么做；或者，通常表示为 "a last resort"
2. 使用触发器。

所以我删除了 ON DELETE CASCADE 和 instead use triggers ( documentation ) 像这样:

CREATE TRIGGER DELETE_User
   ON Users
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON
 DELETE FROM Items WHERE userId IN (SELECT id FROM DELETED)
 DELETE FROM Collections WHERE userId IN (SELECT id FROM DELETED)
 DELETE FROM Users WHERE id IN (SELECT id FROM DELETED)
END

CREATE TRIGGER DELETE_Item
   ON Items
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON
 DELETE FROM CollectedItems WHERE itemId IN (SELECT id FROM DELETED)
 DELETE FROM Items WHERE id IN (SELECT id FROM DELETED)
END

CREATE TRIGGER DELETE_Collection
   ON Collections
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON
 DELETE FROM CollectedItems WHERE collectionId IN (SELECT id FROM DELETED)
 DELETE FROM Collections WHERE id IN (SELECT id FROM DELETED)
END

然而，这失败了，尽管很微妙。我有一堆单元测试(用 xUnit 编写)。单独测试总是通过。但是大量运行一些随机失败并出现 SQL 死锁。在 another answer有人向我指出了 SQL Profiler，它显示了两个删除调用之间的死锁。

解决这些菱形删除级联的正确方法是什么？

Answer 1

我更喜欢不有自动级联操作，无论是删除还是更新。只为安心。想象一下，您已经配置了级联删除，然后您的程序由于某些错误而尝试删除错误的用户，即使数据库中有一些与之相关的数据。相关表中的所有相关数据将在没有任何警告的情况下消失。

通常我确保首先使用明确的单独过程删除所有相关数据，每个相关表一个，然后我删除主表中的行。删除将成功，因为引用表中没有子行。

对于你的例子，我有一个专用的存储过程DeleteUser，带有一个参数UserID，它知道哪些表与用户相关，以及详细信息应该以什么顺序被删除。此过程已经过测试，是删除用户的唯一方法。如果程序的其余部分错误地尝试直接从 Users 表中删除一行，如果相关表中有一些数据，则此尝试将失败。如果错误删除的用户没有任何详细信息，尝试将通过，但至少您不会丢失大量数据。

对于您的架构，过程可能如下所示:

CREATE PROCEDURE dbo.DeleteUser
    @ParamUserID int
AS
BEGIN
    SET NOCOUNT ON; SET XACT_ABORT ON;

    BEGIN TRANSACTION;
    BEGIN TRY
        -- Delete from CollectedItems going through Items
        DELETE FROM CollectedItems
        WHERE CollectedItems.itemId IN
        (
            SELECT Items.id
            FROM Items
            WHERE Items.userId = @ParamUserID
        );

        -- Delete from CollectedItems going through Collections
        DELETE FROM CollectedItems
        WHERE CollectedItems.collectionId IN
        (
            SELECT Collections.id
            FROM Collections
            WHERE Collections.userId = @ParamUserID
        );

        -- Delete Items
        DELETE FROM Items WHERE Items.userId = @ParamUserID;

        -- Delete Collections
        DELETE FROM Collections WHERE Collections.userId = @ParamUserID;

        -- Finally delete the main user
        DELETE FROM Users WHERE ID = @ParamUserID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        ...
        -- process the error
    END CATCH;
END

---

如果您真的想设置级联删除，那么我会定义一个 触发器，仅用于Users 表。同样，级联删除不会有外键，但 Users 表上的触发器将具有与上述过程非常相似的逻辑。