Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

I've been wrestling with this for a while and can't quite figure out what's happening. I have a Card entity which contains Sides (usually 2) - and both Cards and Sides have a Stage. I'm using EF Codefirst migrations and the migrations are failing with this error:

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

Here's my Card entity:

public class Card
{
    public Card()
    {
        Sides = new Collection<Side>();
        Stage = Stage.ONE;
    }

    [Key]
    [Required]
    public virtual int CardId { get; set; }

    [Required]
    public virtual Stage Stage { get; set; }

    [Required]
    [ForeignKey("CardId")]
    public virtual ICollection<Side> Sides { get; set; }
}

Here's my Side entity:

public class Side
{
    public Side()
    {
        Stage = Stage.ONE;
    }

    [Key]
    [Required]     
    public virtual int SideId { get; set; } 

    [Required]
    public virtual Stage Stage { get; set; }

    [Required]
    public int CardId { get; set; }

    [ForeignKey("CardId")]
    public virtual Card Card { get; set; }

}

And here's my Stage entity:

public class Stage
{
    // Zero
    public static readonly Stage ONE = new Stage(new TimeSpan(0, 0, 0), "ONE");
    // Ten seconds
    public static readonly Stage TWO = new Stage(new TimeSpan(0, 0, 10), "TWO");

    public static IEnumerable<Stage> Values
    {
        get
        {
            yield return ONE;
            yield return TWO;
        }

    }

    public int StageId { get; set; }
    private readonly TimeSpan span;
    public string Title { get; set; }

    Stage(TimeSpan span, string title)
    {
        this.span = span;
        this.Title = title;
    }

    public TimeSpan Span { get { return span; } }
}

What's odd is that if I add the following to my Stage class:

    public int? SideId { get; set; }
    [ForeignKey("SideId")]
    public virtual Side Side { get; set; }

The migration runs successfully. If I open up SSMS and look at the tables, I can see that Stage_StageId has been added to Cards (as expected/desired), however Sides contains no reference to Stage (not expected).

If I then add

    [Required]
    [ForeignKey("StageId")]
    public virtual Stage Stage { get; set; }
    public int StageId { get; set; }

To my Side class, I see StageId column added to my Side table.

This is working, but now throughout my application, any reference to Stage contains a SideId, which is in some cases totally irrelevant. I'd like to just give my Card and Side entities a Stage property based on the above Stage class without polluting the stage class with reference properties if possible... what am I doing wrong?

Solution 1:

Because Stage is required, all one-to-many relationships where Stage is involved will have cascading delete enabled by default. It means, if you delete a Stage entity

• the delete will cascade directly to Side
• the delete will cascade directly to Card and because Card and Side have a required one-to-many relationship with cascading delete enabled by default again it will then cascade from Card to Side

So, you have two cascading delete paths from Stage to Side - which causes the exception.

You must either make the Stage optional in at least one of the entities (i.e. remove the [Required] attribute from the Stage properties) or disable cascading delete with Fluent API (not possible with data annotations):

modelBuilder.Entity<Card>()
    .HasRequired(c => c.Stage)
    .WithMany()
    .WillCascadeOnDelete(false);

modelBuilder.Entity<Side>()
    .HasRequired(s => s.Stage)
    .WithMany()
    .WillCascadeOnDelete(false);

Solution 2:

I had a table that had a circular relationship with others and I was getting the same error. Turns out it is about the foreign key which was not nullable. If the key is not nullable the related object must be deleted, and circular relations don't allow that. So use nullable foreign key.

[ForeignKey("StageId")]
public virtual Stage Stage { get; set; }
public int? StageId { get; set; }