January 13, 2026 at 3:25 pm
Not sure if this is really a relational theory question but it seems about the best sub forum for it. Apologies if it's the wrong one.
I have a Person table. I also have a Relationships table. The Relationship acts as a linking entity between two Persons. E.g. John is the father of Ted.
I've modelled this by having the Relationship table having two columns (Person_1 and Person_2) and each of these has a foreign key to the Person table. Fine so far. However, I would like to automatically cascade deletes on those foreign keys. E.g. If John is deleted, it should automatically delete his relationship to Ted. (It should not delete Ted - only the relationship). And vice versa: if Ted is deleted, it should automatically delete his relationship to John.
I'm quite surprised that I don't seem to be able to manage this by simply putting Cascade on Delete on both Foreign Keys but when I try I get an error saying that this is not allowed due to cyclic relationship (I don't believe that's the problem) or multiple cascade paths (I think this is my blocker).
If I'm reading that right it's saying that you can't cascade deletes down two paths that end at the same table. But I don't understand why not. It wouldn't create any logical inconsistencies that I can see, no orphans etc. It seems like it should be a valid operation.
So I guess I've got a few questions:-
January 14, 2026 at 1:17 am
CREATE OR ALTER PROCEDURE dbo.person_delete
@person_id int
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRAN;
DELETE r
FROM dbo.Relationships r
WHERE r.Person_1 = @person_id
OR r.Person_2 = @person_id;
DELETE p
FROM dbo.Person p
WHERE p.PersonId = @person_id;
COMMIT;
END
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 14, 2026 at 4:12 pm
My approach is similar to Steve Collins'. I like stored procedures to handle database logic that doesn't need to be in the application layer. SOME things belong in the application layer, but I feel this is best to have in a stored procedure. I would avoid a trigger for it as there isn't really a need for the trigger - you are going to be using the application to remove a user, so the application should call the stored procedure and wait for the result.
Only change I'd do is have the stored procedure return a value of some sort to the application. An output variable so the application knows the result of the stored procedure (success or failure and on failure, why). I'd also put try catch logic around the DELETE since it is in an explicit transaction - you don't want the query to fail for some reason and not commit and be stuck with an open transaction and long term blocking. If it fails, I'd do an explicit rollback. Another thing that is entirely personal preference is I like to avoid single letter aliases, so I'd not use "r" and "p" for my aliases, but that's just me.
One reason I don't like triggers is that they have to be coded carefully. You don't want the trigger to trigger itself, you don't want the trigger to be too slow and cause a lot of blocking or worse - deadlocks. Stored procedures can cause this too, but I find SP's a LOT easier to tune than a trigger. Triggers have their place and this can be a use case for them, but I find stored procedures to be cleaner and easier to debug.
Just my 2 cents though. There is always more than one solution to almost every database related problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 16, 2026 at 11:00 am
Hi Guys.
Thanks very much for the helpful responses.
"SQL Server wants to guarantee a single parent delete cannot reach the same target row by more than one cascade route. Multiple cascade paths are not allowed"
Yeah, that's what I thought but I don't understand why? You're correct that, in my set up, a person cannot relate directly to themselves but even if they did, I can't see how this would create an inconsistency.
E.g. Johnny is his own Father. We delete the Johnny Person record, it would delete the associated Johnny to Johnny relationship record. The key only goes one way so it wouldn't chain from there and e.g. try to delete the Johnny record again. What would happen is that it would try to delete the relationship record twice which might be a "mechanical" problem for SQL Server engine, I guess, but it shouldn't be a logical problem (logically it's no different to a record matching two parts of a delete statement's where clause). It's kind of a race condition rather than a circular reference. Am I missing something further?
Other than that I'm particularly grateful for the suggestions on how to handle it. They both fall under the general umbrella "you need to handle it yourself" so that give me the confidence I'm not missing some better option. I agree with your reticence about triggers (for me the biggest worry is that they're a bit tucked away and I think they're quite easy for future developers to miss - creating a bit of a maintenance headache) but, thinking about it, I'm stuck with them in my scenario. The persons and relationship table are populated by data feeds that will be written by future developers so I want to police the integrity as close to the table as possible.
Thank you again for your posts.
January 16, 2026 at 5:50 pm
I tend to approach this as Brian has.
As to the Why, we can only guess. Mine is that MS doesn't want to get into an unanticipated situation or cause an issue in the query execution engine from a row being removed already and still targeted by a query plan somehow
January 17, 2026 at 11:25 am
"As to the Why, we can only guess."
Yeah, that's what I thought. Just one of those foibles we have to live with I guess. As long as I'm not misunderstanding some logical restriction I don't mind working around it.
I should say that I generally agree with the approach that Brian and Steve(s) have put forward and would normally use a sproc or app logic. But in my particular scenario (where a future developer will write a data feed that will manipulate the data in the table) I think a trigger's probably better as the manipulation is out of my control.
Thanks very much for all the input.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply