3

Note: this question appears to have been asked before at Cascade delete on many-to-many between same table but didn't receive a satisfactory answer.

I have a table, Friendship( MemberId, FriendId ), which allows a Member to add another Member as a friend. 

CREATE TABLE dbo.FRIENDSHIP(
         MemberId         INT NOT NULL
       , FriendId         INT NOT NULL
       , DateCreated      DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
  CONSTRAINT pk_friendship PRIMARY KEY( MemberId, FriendId ),
  CONSTRAINT fk_friendship_member FOREIGN KEY( MemberId ) REFERENCES Member( MemberId ) ON DELETE CASCADE,
  CONSTRAINT fk_friendship_friend FOREIGN KEY( FriendId ) REFERENCES Member( MemberId ) ON DELETE CASCADE
);

When I run this script, I see the following error:

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

Obviously, I want records in Friendship to be deleted whenever either member is deleted; I also don't want either field to be nullable.

After some research, I saw people suggesting the use of Triggers. So I created one: 

CREATE OR ALTER TRIGGER friendship_cascade_delete
    ON Member
   FOR DELETE
    AS
DELETE FROM Friendship
 WHERE MemberId IN( SELECT MemberId
                      FROM deleted )
    OR FriendId IN( SELECT MemberId
                     FROM deleted );

but when I go to delete a Member, I still get the error:

The DELETE statement conflicted with the REFERENCE constraint "fk_friendship_member". The conflict occurred in database "CVGS", table "dbo.FRIENDSHIP", column 'MemberId'.

So it's not even getting to the point where it can fire the trigger. If I remove fk_friendship_member and fk_friendship_friend, the trigger works properly - but I'm not sure I want to remove those constraints.

Is there any way to get the behaviour I want, without risking invalid rows in the table or unnecessary SQL errors? Am I missing something obvious here?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Doug Epp
  • 35
  • 5

1 Answers1

2

Use an INSTEAD OF trigger, instead of an AFTER trigger. EG

    --DROP TABLE IF EXISTS FRIENDSHIP 
    --DROP TABLE IF EXISTS MEMBER
        GO

      CREATE TABLE MEMBER(MemberID int primary key);

      CREATE TABLE dbo.FRIENDSHIP(
             MemberId         INT NOT NULL
           , FriendId         INT NOT NULL
           , DateCreated      DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
      CONSTRAINT pk_friendship PRIMARY KEY( MemberId, FriendId ),
      CONSTRAINT ak_friendship UNIQUE( FriendId, MemberId ),
      CONSTRAINT fk_friendship_member FOREIGN KEY( MemberId ) REFERENCES Member( MemberId ), 
      CONSTRAINT fk_friendship_friend FOREIGN KEY( FriendId ) REFERENCES Member( MemberId ) 
    );

        GO

CREATE OR ALTER TRIGGER friendship_cascade_delete
    ON Member
   INSTEAD OF DELETE
    AS
    BEGIN
        SET NOCOUNT ON;
        DELETE FROM Friendship
         WHERE MemberId IN( SELECT MemberId FROM deleted );

        DELETE FROM Friendship
         WHERE FriendId IN( SELECT MemberId FROM deleted );

        DELETE FROM MEMBER
          WHERE MemberId IN( SELECT MemberId FROM deleted );
    END

    GO 
    INSERT INTO MEMBER(MemberID) values (1),(2),(3)

    INSERT INTO FRIENDSHIP(MemberID, FriendId) values (1,2), (1,3), (3,2)

    go
    select *
    from FRIENDSHIP

    delete from member where MemberID  = 1

    select *
    from FRIENDSHIP
Doug Epp
  • 35
  • 5
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Edited the example. It's important to have an index with each FK leading, or else the cascade delete will always require a table scan, leading to higher cost and deadlocks. Also it will promote granular locking to do the linking table deletes in two separate statements, and will probably be cheaper too. – David Browne - Microsoft Oct 18 '17 at 18:50