在SQL中级联菱形删除

一尘不染

在SQL中级联菱形删除

sql

如果我的数据库中有一个简单的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)

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

重新设计表格，但是我看不到如何做。或者，通常被称为“不得已而为之”
使用触发器。

因此，我删除了ON DELETECASCADE并改为使用触发器（文档），如下所示：

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死锁，大量运行会随机失败。

阅读 202

2021-03-08

共1个答案

一尘不染

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

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

对于您的示例，我将有一个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表上的触发器将具有与上述过程非常相似的逻辑。

2021-03-08