Two foreign keys reference one table - ON UPDATE SET NULL doesn't work

Multiple Cascading Actions

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.

Possibly in situations like this you might want to consider to implement functionality to delete user logically rather then physically (e.g. by introducing a flag field Active or Deleted in Users table). That way all relationships stay intact and can be analyzed retrospectively.

But if you still need to implement ON DELETE SET NULL for both FK's you can use a FOR DELETE trigger on User table like this:

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

One alternative is to create a cross reference table between table A and table B where each entry is A.ID and B.ID and B.ID has a foreign key to B. Then you can simply CASCADE deletes to the cross reference. You will need to put a third field in your cross reference to state the unique purpose of reference such as

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

Naturally, you would then take those fields out of table A. The left join will then give you null for those fields if they are missing.

I don't think it's possible (in SQL Server) to do it on 2 or more FK constraints on the same table, pointing to the same FK.

Normally in situations like this you'd rather delete user logically then physically by introducing a flag field (e.g. Active or Deleted). That way all relationships stay intact and can be analyzed retrospectively. --- peterm

If you want to stick with the original idea of setting NULL, a way around the problem would be to handle your deletion of users in a stored procedure and have it perform the updates immediately afterwards.

CREATE PROCEDURE sp_DeleteUser     @UserId INTASBEGIN    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;ENDGO