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

Question

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?

Answer 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);