Geek asks and answers

Two foreign keys refer to the same table - ON UPDATE SET NULL does not work

I have two foreign keys in the table. Suppose a table is called Newsand has foreign keys updatedByIdand createdById, both of which point to userIda table Users.

Now I want to set NULLforeign keys when deleting the user, but when I try to install ON DELETE SET NULLin these relationships, I get:

Representing the FOREIGN KEY constraint 'FK_News_Users' in the News table can cause loops or multiple cascading paths. Specify ON. DELETE NO ACTION or ON UPDATE NO ACTION or modify other FOREIGN KEY restrictions.

I do not understand why both foreign keys cannot set to null?

+5
source share
3 answers

Several cascading actions

A series of cascading reference actions initiated by a single DELETE or UPDATE should form a tree that does not contain a circular Recommendation. A table cannot appear more than once in the list of all cascading reference actions that result from DELETE or UPDATE. In addition, the cascading referential tree should not have more than one path to any given table. Any tree branch is completed when it encounters a table for which NO ACTION is specified, or by default.

, , (, Active Deleted in Users). , .

ON DELETE SET NULL FK, FOR DELETE User :

CREATE TRIGGER Users_News_Delete_Trigger 
ON Users FOR DELETE
AS BEGIN
    UPDATE News SET createdById = NULL 
     WHERE createdById = DELETED.id;
    UPDATE News SET updatedById = NULL 
     WHERE updatedById = DELETED.id;
END
+7

A B, A.ID B.ID, B.ID B. CASCADE , , ,

[NewsID] INT NOT NULL DEFAULT 0,
[UsersID] INT NOT NULL DEFAULT 0,
[IsCreatedBy] bit NOT NULL DEFAULT 0

, A. null , .

+3

, ( SQL Server) 2 FK , FK.

, , (, Active Deleted). , .   --- peterm

If you want to stick with the original idea of ​​setting NULL, the solution would be to handle the removal of users in the stored procedure and immediately perform updates.

CREATE PROCEDURE sp_DeleteUser 
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM Users WHERE Id = @UserId;

    UPDATE News SET created_byId = NULL WHERE created_byId = @UserId;

    UPDATE News SET updated_byId = NULL WHERE created_byId = @UserId;
END
GO
+1
source

More articles:


All Articles