SQL Server – Handling Multiple Foreign Keys and Cascade Delete

cascadeforeign keysql serversql server 2014

enter image description here
In SQL Server 2014, I'm trying to add CASCADE DELETING (I want to set field to null actually, but is the same ) on 3 FK. If I add a Cascade Delete in one relationship, it works fine. If I add more Cascade Deletes, it doesn't work (Cycle detected error message).

In the above diagram, you can see the Users table, and a Tasks table ("Tareas" in spanish). So, what I need to acomplish is when the user is deleted, I need to set the marked field in Tasks to NULL.

This is something common in a database, so I thought there is a way to handle this.

In my case, most of my tables have a pair of fields holding the UserId of the user that Created or Modified the record. So, I need to solve this pattern to apply it several places.

Best Answer

You can create a trigger to update the UserID columns in related tables, such as:

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;
*/

However, instead of deleting rows from the Users table, I would recommend updating a bit column to indicate the User has been deleted. This allows a history of users, along with the actions taken by those users in the Tasks table. For instance:

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;
Related Question