Why can't SQL Server handle cascading deletes for two foreign keys pointing to the same table?

I'm using Entity Framework - Code First, and I've run into an issue where I have to disable cascading deletes for a particular foreign key.

Here is my entity class:

public class ChallengeMatch
{
    public int Id { get开发者_如何学Python; set; }
    public int ChallengerClubMemberId { get; set; }
    public int ChallengeeClubMemberId { get; set; }
    public bool ChallengerWon { get; set; }
    public string Score { get; set; }

    public virtual ClubMember ChallengerClubMember { get; set; }
    public virtual ClubMember ChallengeeClubMember { get; set; }
}

If I allow Code First to generate the database for this table with all default settings (which includes cascading deletes), it will throw an exception when it tries to generate the database objects.

If I add the following code to my DbContext class, I no longer get an exception, but now cascading deletes will only somewhat work:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<ChallengeMatch>()
        .HasRequired(cm => cm.ChallengeeClubMember)
        .WithMany()
        .HasForeignKey(cm => cm.ChallengeeClubMemberId)
        .WillCascadeOnDelete(false);
}

Because of this, I have to write code by hand. Specifically, if I'm about to delete ClubMember 13, I have to delete any ChallengeMatch whose ChallengeeClubMemberId is 13.

What I don't understand is why any of this should be necessary. Why can't SQL Server handle a cascading delete even if there are two foreign keys pointing to the same table? I can't think of any reason why this would fail.

It should be a simple three-step process (psuedo code):

1. Delete all challenge matches where ChallengerClubMemberId == 13
2. Delete all challenge matches where ChallengeeClubMemberId == 13
3. Delete the club member where Id = 13.

Why can't SQL Server do this or why does it choose not to?

---

The problem is that your configuration allows multiple cascade delete paths. This can happen if both your properties will point to the same ClubMember. SQL Server doesn't allow that. It is more about internal implementation of SQL server and more details can be found in this answer. I think that this check is simple and safe solution to avoid some race conditions during deletes cascading in parallel.