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

Just for documentation purpose, to someone that comes on the future, this thing can be solved as simple as this, and with this method, you could do a method that disabled one time, and you could access your method normally

Add this method to the context database class:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {
    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
}

Answer 2

I was getting this error for lots of entities when I was migrating down from an EF7 model to an EF6 version. I didn't want to have to go through each entity one at a time, so I used:

builder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
builder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

Answer 3

I had this issue also, I solved it instantly with this answer from a similar thread

In my case, I didn't want to delete the dependent record on key deletion. If this is the case in your situation just simply change the Boolean value in the migration to false:

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

Chances are, if you are creating relationships which throw this compiler error but DO want to maintain cascade delete; you have an issue with your relationships.

Answer 4

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 key is not nullable related object must be deleted and circular relations doesnt allow that. So use nullable foreign key.

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

Answer 5

Anybody wondering how to do it in EF core:

      protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                foreach (var relationship in modelBuilder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
                {
                    relationship.DeleteBehavior = DeleteBehavior.Restrict;
                }
           ..... rest of the code.....

Answer 6

This sounds weird and I don't know why, but in my case that was happening because my ConnectionString was using "." in "data source" attribute. Once I changed it to "localhost" it workded like a charm. No other change was needed.