几个外键和级联删除SQL Server

Question

在Server 2014中，我试图在3 FK上添加级联删除(我希望将字段设置为null，但相同)。如果我在一个关系中添加一个级联删除，它可以正常工作。如果我添加更多的级联删除，它不工作(循环检测的错误消息)。

在上面的图表中，您可以看到User表和一个任务表(西班牙语中的“Tareas”)。因此，当用户被删除时，我需要将任务中的标记字段设置为NULL。

这在数据库中是很常见的，所以我想有一种方法来处理这个问题。

在我的例子中，我的大多数表都有一对字段，保存创建或修改记录的用户的UserId。所以，我需要解决这个模式来应用几个地方。

Answer 1

您可以创建一个触发器来更新相关表中的UserID列，例如：

USE Tempdb;

CREATE TABLE dbo.Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , ApplicationID INT NOT NULL
    , Country VARCHAR(255) NOT NULL
    , DefaultTenantID INT NOT NULL
);
GO
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL
    , TenantID INT NOT NULL
    , CreatedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_CreatedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , ModifiedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_ModifiedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , AssignedTo_UserID INT NULL
        CONSTRAINT FK_Tasks_AssignedTo
        FOREIGN KEY REFERENCES dbo.Users(UserID)
);
GO
INSERT INTO dbo.Users (UserID, ApplicationID, Country, DefaultTenantID)
VALUES (1, 1, 'CA', 1);

INSERT INTO dbo.Tasks (TaskID, TenantID, CreatedBy_UserID, ModifiedBy_UserID, AssignedTo_UserID)
VALUES (1, 1, 1, NULL, NULL);
GO

CREATE TRIGGER TG_Users_Trigger
ON dbo.Users
INSTEAD OF DELETE
AS
BEGIN
    UPDATE dbo.Tasks 
    SET CreatedBy_UserID = NULL 
    WHERE EXISTS (
        SELECT 1 
        FROM deleted d
        WHERE d.UserID = CreatedBy_UserID
        );
    UPDATE dbo.Tasks 
    SET ModifiedBy_UserID = NULL 
    WHERE EXISTS (
        SELECT 1 
        FROM deleted d
        WHERE d.UserID = ModifiedBy_UserID
        );
    UPDATE dbo.Tasks 
    SET AssignedTo_UserID = NULL 
    WHERE EXISTS (
        SELECT 1 
        FROM deleted d
        WHERE d.UserID = AssignedTo_UserID
        );
    DELETE
    FROM dbo.Users 
    WHERE EXISTS (
        SELECT 1 
        FROM deleted d
        WHERE d.UserID = UserID
        );
END
GO

DELETE 
FROM dbo.Users;

SELECT *
FROM dbo.Users;
SELECT *
FROM dbo.Tasks;

/* - cleanup removed for safety
DROP TRIGGER TG_Users_Trigger;
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Users;
*/

但是，我不建议从User表中删除行，而是建议更新位列以指示用户已被删除。这允许用户的历史记录，以及任务表中的用户所采取的操作。例如：

CREATE TABLE dbo.Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , ApplicationID INT NOT NULL
    , Country VARCHAR(255) NOT NULL
    , DefaultTenantID INT NOT NULL
    , IsDeleted BIT NOT NULL /* Default this to false */
        CONSTRAINT DF_Users_IsDeleted DEFAULT ((0))
);

CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL
    , TenantID INT NOT NULL
    , CreatedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_CreatedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , ModifiedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_ModifiedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , AssignedTo_UserID INT NULL
        CONSTRAINT FK_Tasks_AssignedTo
        FOREIGN KEY REFERENCES dbo.Users(UserID)
);

/* Note we are not specifying the state of the IsDeleted column here
    since it defaults to zero, which indicates they are active
*/
INSERT INTO dbo.Users (UserID, ApplicationID, Country, DefaultTenantID)
VALUES (1, 1, 'CA', 1);

INSERT INTO dbo.Tasks (TaskID, TenantID, CreatedBy_UserID, ModifiedBy_UserID, AssignedTo_UserID)
VALUES (1, 1, 1, NULL, NULL);

/*  This is how we "delete" a user */
UPDATE dbo.Users
SET IsDeleted = 1
WHERE UserID = 1;

/* Any queries that access the Tasks table now need to be cognizant
    of the state of the user (deleted or not)
*/
SELECT T.*
FROM dbo.Tasks T
    INNER JOIN dbo.Users U ON T.CreatedBy_UserID = U.UserID
WHERE U.IsDeleted = 0;