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

Question

I've got two foreign keys in a table. Let's assume that table is called News and has foreign keys updatedById and createdById, both of which point to userId in table Users.

Now I want to set to NULL foreign keys when user is deleted, but when I try to set ON DELETE SET NULL in that relationships I get:

Introducing FOREIGN KEY constraint 'FK_News_Users' on table 'News' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I don't understand why both foreign keys can't set to null?

Can you post the complete SQL DDL for the tables "News" and "Users"? — Mike Sherrill 'Cat Recall', Jan 28 '13 at 00:13
Also tag the question with the dbms you're using. (SQL Server, Oracle, MySQL, PostgreSQL, etc.) — Mike Sherrill 'Cat Recall', Jan 28 '13 at 00:20
sample database: https://dl.dropbox.com/u/105509493/test.sql try to modify relationship: FK_News_Users_updated - ON DELETE to Set Null and save. You'll see that error. — Wojciech Kulik, Jan 28 '13 at 00:39
So you're allowing orphaned News records to exist without owners? Does that make sense? — Mike Parkhill, Jan 28 '13 at 00:45
Please don't consider sense of it, but the problem. This tables aren't my real tables, I've created this sample to simplify problem. Btw. this sample has sense, because user can be removed, but news should stay. — Wojciech Kulik, Jan 28 '13 at 00:50
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, Jan 28 '13 at 01:09

peterm · Accepted Answer · 2013-01-28T04:29:57.753

10

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 DELETE
AS BEGIN
    UPDATE News SET createdById = NULL 
     WHERE createdById = DELETED.id;
    UPDATE News SET updatedById = NULL 
     WHERE updatedById = DELETED.id;
END

edited Jan 28 '13 at 04:29

answered Jan 28 '13 at 01:36

peterm

91,357
15
148
157

Yes, I've thought also about triggers, but could you explain why this constraints, which I've tried to create doesn't work? – Wojciech Kulik Jan 28 '13 at 01:41
You can read about it [here](http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths) – peterm Jan 28 '13 at 01:52
1

*"Normally in situations like this you'd better implement functionality to delete user logically rather then physically (e.g. by introducing a flag field Active or Deleted in Users table)."* That might be a good idea in some applications. It's not a good idea in every application. `ON DELETE SET NULL` and `ON DELETE CASCADE` exist for a reason. – Mike Sherrill 'Cat Recall' Jan 28 '13 at 02:09
4

@WojciechKulik: It's strictly a quality-of-implementation issue. Other platforms handle your scenario correctly, setting both referencing values to NULL. For example, PostgreSQL. – Mike Sherrill 'Cat Recall' Jan 28 '13 at 02:11
@Catcall *It's not a good idea in every application.* I won't argue with that. But the question introduced a very specific scenario where after deleting a user you lose information about who created and/or updated news. And in that particular scenario(s) IMHO that is not a best choice. – peterm Jan 28 '13 at 02:17
4

In foreign keys declared `on delete set null`, removing information is *exactly* the action the database designer decided was appropriate. That's why both `on delete set null` and `on delete cascade` exist--to remove information--and why they've been part of the SQL standards since 1992. (And those aren't the OP's real tables. The question isn't about whether `on delete set null` is appropriate. The question is why it fails under these circumstances in SQL Server.) – Mike Sherrill 'Cat Recall' Jan 28 '13 at 03:45
@Catcall Both why it happens in SQL Server and how possibly to overcome this, if OP wants to, IMHO were provided in the answer. BTW cascading referential integrity checks in SQL Server also exist for a reason. But thank you for your input though. I even changed the wording in the answer about the proposed design for that matter. – peterm Jan 28 '13 at 04:36
1

Hello, I had to use an INSTEAD OF DELETE trigger as doing a FOR DELETE still meant the FK constraint prevented the original delete. – Marlon Jan 17 '14 at 12:11

Park · Answer 2 · 2013-12-18T23:13:48.810

3

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.

edited Dec 18 '13 at 23:13

answered Dec 18 '13 at 21:48

Park

146
3

My university lecturer who covered normalisation theory salutes you. – matao Nov 16 '16 at 22:13

PeteGO · Answer 3 · 2013-01-28T11:36:07.843

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 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

*"I don't think it's possible to do it on 2 or more FK constraints on the same table, pointing to the same FK."* Just to be clear, it's possible in SQL. It's just not possible in SQL Server. — Mike Sherrill 'Cat Recall', Jan 28 '13 at 02:15

Emre · Answer 4 · 2022-04-18T03:44:11.413

Multiple Delete/Update Rules when multiple foreign keys reference one table:

Years after you asked this question, I had the same problem.

I have Currencies and Products tables (i simplified for sharing) in my database as in the diagram:

I needed to SET NULL both columns BuyingCurrencyId and SellingCurrencyId when I delete a record from table Currencies. SQL Server does not support multiple INSERT And UPDATE Specification in a structure like this.

In this case, a temporary solution can be produced by adding a boolean IsDeleted column to table Currencies. But in a large database, even if the DeleteRule || UpdateRule specifications is passed around with such a tricky solution in a table, it will definitely be necessary somewhere in the database.

I got the following conflict occurred in database error in all of the triggers I created before:

And finally I was able to solve the problem by creating the following trigger:


USE [FooBarDatabase]
GO

--DROP TRIGGER accounting.Currency_Delete_Trigger
--GO

CREATE TRIGGER accounting.Currency_Delete_Trigger
ON accounting.Currencies INSTEAD OF DELETE
AS BEGIN
    UPDATE stock.Products
      SET BuyingCurrencyId = NULL,
          SellingCurrencyId = NULL
      WHERE BuyingCurrencyId in (SELECT Id FROM deleted)
      AND   SellingCurrencyId in (SELECT Id FROM deleted);
    DELETE accounting.Currencies 
      WHERE Id in (SELECT Id FROM deleted);
END
GO

ALTER TABLE [accounting].[Currencies] 
   ENABLE TRIGGER [Currency_Delete_Trigger]
GO

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

4 Answers4