Several Foreign Key and Cascade Delete SQL Server

Question

In SQL Server 2014, I'm trying to add CASCADE DELETING 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.

Answer 1

CASCADE DELETE means that in your situation if you delete a User, then SQL Server will delete any attached Tasks too. That is, entire rows will be deleted. Apart from issues such as unexpected losses of data, loss of referential integrity or the potential of infinitely recursive deletions, this behaviour is not what you want anyway. You have stated you only want to mark the associate User columns in your Tasks table to null.

As a suggestion, have you considered implementing a TRIGGER? Something like this (haven't tested this, treat it as pseudo-code!):

CREATE TRIGGER DeleteTareasTrigger
ON Users
BEFORE DELETE AS
BEGIN

UPDATE t
SET t.CreadaPor = NULL
FROM DELETED as d
INNER JOIN Tareas t ON d.UserID = t.CreadaPor

UPDATE t
SET t.ModifcadaPor = NULL
FROM DELETED as d
INNER JOIN Tareas t ON d.UserID = t.ModifcadaPor

END
GO

Or as another approach, add a bit field on the User table to indicate whether the person is active/deleted.