Two foreign keys to the same table. Can't cascade deletes.

• FunkyDexter

  SSCarpal Tunnel

  Points: 4064

  More actions

  January 13, 2026 at 3:25 pm

  #4716049

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

  1. Am I correct that this isn't allowed or have I just done something dumb? (always a possibility)
  2. If this isn't allowed, why not?
  3. How do other people go about modelling this scenario?  I guess I could do it with triggers or application logic but that seems awfully clumsy.

  • This topic was modified 10 hours, 56 minutes ago by FunkyDexter.

• Steve Collins

  SSCrazy

  Points: 2244

  More actions

  January 14, 2026 at 1:17 am

  #4716310

  1.  It's not allowed.
  2. 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.  Maybe your data is well organized and has no issues, but what if Ted is the progenitor of Ted, or Ted is an ancestor of himself?
  3. With a stored procedure that either performs a transaction with both deletes or does a rollback.  "I guess I could do it with triggers or application logic"  If the choice is between "application logic" (which seems to suggest NOT stored procedure) then TRIGGER might be the better option

     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