Introducing FOREIGN KEY constraint may cause cycle

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

You can set cascadeDelete to false or true (in your migration Up() method). Depends upon your requirement.

AddForeignKey("dbo.Stories", "StatusId", "dbo.Status", "StatusID", cascadeDelete: false);

Answer 2

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

Answer 3

In .NET Core I changed the onDelete option to ReferencialAction.NoAction

         constraints: table =>
            {
                table.PrimaryKey("PK_Schedule", x => x.Id);
                table.ForeignKey(
                    name: "FK_Schedule_Teams_HomeId",
                    column: x => x.HomeId,
                    principalTable: "Teams",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.NoAction);
                table.ForeignKey(
                    name: "FK_Schedule_Teams_VisitorId",
                    column: x => x.VisitorId,
                    principalTable: "Teams",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.NoAction);
            });

Answer 4

In .NET Core I played with all upper answers - but without any success. I made changes a lot in DB structure and every time added new migration attempting to update-database, but received the same error.

Then I started to remove-migration one by one until Package Manager Console threw me exception:

The migration '20170827183131_***' has already been applied to the database

After that, I added new migration (add-migration) and update-database successfully

So my suggestion would be: clear out all your temp migrations, until your current DB state.

Answer 5

None of the aforementioned solutions worked for me. What I had to do was use a nullable int (int?) on the foreign key that was not required (or not a not null column key) and then delete some of my migrations.

Start by deleting the migrations, then try the nullable int.

Problem was both a modification and model design. No code change was necessary.

Answer 6

I fixed this. When you add the migration, in the Up() method there will be a line like this:

.ForeignKey("dbo.Members", t => t.MemberId, cascadeDelete:True)

If you just delete the cascadeDelete from the end it will work.