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.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply