Foreign key constraints to the same table may cause cycles or multiple cascade paths

Question

I have a table of Transaction(Id, Comment, ClosingTransactionId). I would like the implement the "set null" functionality at deleting.

So if I have two rows:

(1, opening transaction, 2)

(2, closing transaction, null)

and I delete the second row, then the ClosingTransactionId of the first row should be set to null. I can add a foreign key constraint to ClosingTransactionId with "No action", but not with "set null" because SQL Server raises "cycles or multiple cascade paths".

Some page explain the problem, https://stackoverflow.com/a/12683993/5852947, some suggest to use triggers, https://stackoverflow.com/a/852047/5852947, other says anything is better than a trigger. Which way should I go? (Of course I could set ClosingTransactionId of the first row to null and then delete the second row, but I really would like the database would handle this.)

I use EF6 if it is any help.

No, it's not possible in a self-referencing relation, because the `DELETE` can/may result to all rows being deleted and can't do `DELETE/UPDATE` twice on the same table. You may handle the situation in different ways... An `AFTER DELETE TRIGGER` maybe — Ilyes, Sep 07 '20 at 20:01

David Browne - Microsoft · Accepted Answer · 2020-09-07T22:32:49.060

I really would like the Db would handle this

Then you need an INSTEAD OF DELETE trigger. SQL Server disallows cascade deletes in many situations where it would otherwise make sense, partly because you can always use a trigger to implement the behavior.

They aren't evil, just easy to get wrong.

eg

use tempdb
drop table if exists [transaction]
go
create table [Transaction]
(
  Id int primary key, 
  Comment nvarchar(200), 
  ClosingTransactionId int null references [Transaction]
)

insert into [Transaction]
values (1,'opening transactin',null),(2,'closing transaction',null)

update [Transaction] set ClosingTransactionId = 2 where id = 1


delete from [Transaction] where id = 2
--Msg 547, Level 16, State 0, Line 17
--The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK__Transacti__Closi__29572725". The conflict occurred in database "tempdb", table "dbo.Transaction", column 'ClosingTransactionId'.

go

create or alter trigger TransactionDelete  
  on [Transaction]
  instead of delete
as
begin
  set nocount on

  update [Transaction] set ClosingTransactionId = null
  where ClosingTransactionId in (select id from deleted)

  delete from [Transaction] 
  where id in (select id from deleted)

end

go
delete from [Transaction] where id = 2
--(1 row affected)

And for EF you should still have the model configured for cascade deletes, but prevent it from attempting to create the foreign key in the database. You'll create that without cascade delete and add the trigger.

score 0 · Answer 2 · answered Sep 08 '20 at 06:53

Instead of self-referencing FK, you can go for different design, to avoid multiple cascading paths and also avoiding triggers.

The below SO post got me this idea: https://stackoverflow.com/a/3548225/634935

TransactionReferenceTable

+------------------------------------------------------------------------+
|                      TransactionReferenceId (PK)                       |
+------------------------------------------------------------------------+
| 1                                                                      |
| 2  --> Deleting this transaction will lead to child being set as NULL  |
| 3                                                                      |
+------------------------------------------------------------------------+

TransactionDetailTable


+-------------------------+--------------------------+--------------------------------------------------------------+
| TransactionSurrogateKey | OpeningTransactionId(FK) |                  ClosingTransactionId (FK)                   |
+-------------------------+--------------------------+--------------------------------------------------------------+
|                       1 |                        1 | 2 --> On deletion of transaction ref, it will be set as null |
+-------------------------+--------------------------+--------------------------------------------------------------+

If I understand well you suggest to divide the Transaction table to TransactionReferenceTable {Id, Comment, TransactionDetailTableId} and TransactionDetailTable. {Id, OpeningTransactionId, ClosingTransactionId }. But if it is true then still there is a loop between the two tables. What am I missing? Should I omit TransactionDetailTableId? — Istvan Heckl, Sep 08 '20 at 11:05
@IstvanHeckl, For multiple cascade paths, there will be issues, if there is more than one path to the child table. So, what we are doing is, single parent(TransactionReference Table), single child(TransactionDetail Table). Single parent table is the master list of transactions. Single child is the detail list of transactions. When you delete a parent, child will be set as null. I think this will solve the problem. I am not very sure. Just give it a try. — Venkataraman R, Sep 08 '20 at 11:55

Foreign key constraints to the same table may cause cycles or multiple cascade paths

2 Answers2