0

This is the relevant table structure:

enter image description here

After which I am running the following queries to add the foreign key restraints:

ALTER TABLE [members].[tbl_FriendInvite]
    ADD FOREIGN KEY ([FromMemberID]) REFERENCES [members].[tbl_Member] ([MemberID]) ON DELETE CASCADE ON UPDATE NO ACTION
GO

ALTER TABLE [members].[tbl_FriendInvite]
    ADD FOREIGN KEY ([ToMemberID]) REFERENCES [members].[tbl_Member] ([MemberID]) ON DELETE CASCADE ON UPDATE NO ACTION
GO

Trying to run this gives the following error:

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

I don't understand what the danger is here. When a row in tbl_Member is deleted it should then cascade and delete all friend invites which have that MemberID as one of the FromMemberID or ToMemberID columns on tbl_FriendInvite. Is the fact that it may be deleting multiple Friend Invites the reason it is warning against "multiple cascade paths"? That's exactly what I want!

I am using SQL Server btw.

Have I made an error in my queries or have I misunderstood how foreign keys work?

AlwaysNeedingHelp
  • 1,851
  • 3
  • 21
  • 29
  • Personally I just never use the `CASCADE` options because they have a history of bugs and a "bare minimum use case" feature set. In this case I think the problem is that deleting a member could theoretically try to delete the same row twice (e.g. if from and to member IDs were the same - which may not be possible, but the foreign key creation doesn't know that, even if a check constraint is explicitly defined). I don't know if there's a way around this limitation using `CASCADE`. Also maybe consider the case where you delete two members and they have both invited each other? – Stuck at 1337 Nov 04 '22 at 19:51
  • Hmm.. How would you handle the deletion in this case? – AlwaysNeedingHelp Nov 04 '22 at 19:55
  • 1
    Stored procedure where you work out exactly which rows should be deleted. – Dale K Nov 04 '22 at 19:55
  • Potential duplicates : https://stackoverflow.com/q/851625/20091109 or https://stackoverflow.com/q/5958323/20091109 – Stuck at 1337 Nov 04 '22 at 19:56
  • If I can't determine the child rows to delete _as a part of the member delete_, I would use a trigger. – Stuck at 1337 Nov 04 '22 at 19:56

0 Answers0