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?

Disable cascading delete by allowing null values in the references... so in `Side` Class add Nullable integer and remove `[Required]` attribute => `public int? CardId { get; set; }` — Jaider, Jan 23 '14 at 22:45
In the EF Core, You should disable cascade delete with `DeleteBehavior.Restrict` or `DeleteBehavior.SetNull`. — Sina Lotfi, Feb 02 '19 at 14:08
The accepted answer is the only correct answer. The question is: how to prevent a circular cascade path if I want a *required* relationship. One simple mapping instruction suffices. So don't suggest to make the relationship optional, or worse, to edit the generated migration file (introducing a discrepancy between the db model and the conceptual model), or worse yet, to disable all cascaded deletes. — Gert Arnold, Nov 08 '20 at 08:53

score 415 · Accepted Answer · edited Jun 05 '18 at 15:10

415

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

edited Jun 05 '18 at 15:10

Riz

5,680
16
56
91

answered Jun 15 '13 at 20:14

Slauma

167,754
56
385
407

3

Thanks Slauma. If I use fluent API as you've demonstrated above, will other fields retain their cascade delete behavior? I still need Sides to be deleted when cards are deleted, for example. – SB2055 Jun 15 '13 at 20:53
1

@SB2055: Yes, it will only affect the relationships from `Stage`. Other relationships remain unchanged. – Slauma Jun 15 '13 at 21:08
2

Is there any way to know wich properties are causing the error? I'm having the same problem, and looking at my classes I can´t see where is the cycle – Rodrigo Juarez May 27 '17 at 01:00
4

Is this a limitation in their implementation? Seems fine to me for a `Stage` deletion to cascade down to `Side` both directly and through a `Card` – aaaaaa Jul 04 '17 at 22:20
1

Suppose we set CascadeOnDelete to false. Then we removed a stage record which is related with one of the Card records. What happens to Card.Stage (FK)? Does it remain same? or is it set to Null? – ninbit Dec 28 '18 at 23:40
What i you want this cascade behavior? In the case of a hierarchical entity structure, that is expected to cascade in both the hierarchy, and if the referenced entity is deleted? – Douglas Gaskell Mar 26 '20 at 20:19

score 77 · Answer 2 · edited Mar 24 '21 at 13:03

77

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

edited Mar 24 '21 at 13:03

Null

1,940
9
24
29

answered Jul 03 '15 at 15:05

Cem Mutlu

1,591
1
21
22

6

I removed [Required] tag but anther important thing was to use `int?` instead of `int` to let it be nullable. – VSB Mar 28 '16 at 17:45
1

I tried many different ways of turning off cascade delete and nothing worked - this fixed it! – ambog36 Oct 05 '16 at 20:57
9

You shouldn't do this if you don't want to allow Stage to be set to null (Stage was a required field in the original question). – cfwall Nov 15 '17 at 10:26
This is the Perfect Fix. It saved my Day!! – lokanath das Nov 06 '20 at 14:48

score 42 · Answer 3 · answered Dec 21 '16 at 18:10

42

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.....

answered Dec 21 '16 at 18:10

Nexus23

5,607
8
43
64

5

That would turn off cascade delete on all the relationships. Cascade delete may be a desired feature for some use cases. – Blaze Jun 06 '17 at 10:29
20

Alternatively, `builder.HasOne(x => x.Stage).WithMany().HasForeignKey(x => x.StageId).OnDelete(DeleteBehavior.Restrict);` – Biscuits Apr 04 '18 at 11:30
@Biscuits Either the extension methods changed over time or you forgot the `builder _ .Entity() _` before `HasOne() ` can be called... – ViRuSTriNiTy May 18 '20 at 13:51
1

@ViRuSTriNiTy, my snippet is 2 years old. But, I think you're right - nowadays it would be for when you opt to implement `IEntityTypeConfiguration`. I don't recall seeing the `builder.Entity` method those days, but I could be wrong. Nevertheless, they'll both work :) – Biscuits May 20 '20 at 13:18

score 25 · Answer 4 · answered Apr 15 '16 at 07:19

25

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

answered Apr 15 '16 at 07:19

Sean

12,296
9
66
104

2

This should be added in the class(es) that inherit from DbContext e.g. in the OnModelCreating method. The builder is of type DbModelBuilder – CodingYourLife Dec 15 '17 at 09:35
This worked for me; .NET 4.7, EF 6. One stumbling block was I got the error, so when I regenerated by migration script with these conventions removed, it didn't APPEAR to help. Running the "Add-Migration" with "-Force" cleared it all, and rebuilt it including these conventions above. Problem solved... – James Joyce Jun 14 '18 at 16:35
Those don't exist in .net core, any equivalent there? – jjxtra Jul 29 '19 at 01:13
@jjxtra check https://stackoverflow.com/questions/46526230/disable-cascade-delete-on-ef-core-2-globally – Sean Jul 29 '19 at 06:35

score 21 · Answer 5 · edited Jun 02 '16 at 08:54

21

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

edited Jun 02 '16 at 08:54

Nozim Turakulov

769
1
8
19

answered Feb 21 '15 at 17:21

Musakkhir Sayyed

6,208
13
37
60

2

@Mussakkhir thank you for you answer. Your way is very elegant and more over - it's more accurate and targeted directly to the problem i faced with! – Nozim Turakulov Jun 02 '16 at 07:03
Just dont forget the `UP` method might be modified by external operations. – Rafael Herscovici Nov 12 '18 at 11:41

score 13 · Answer 6 · answered Oct 10 '17 at 01:24

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

score 9 · Answer 7 · edited Jul 25 '17 at 11:00

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.

score 6 · Answer 8 · edited Nov 12 '18 at 11:42

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.

edited Nov 12 '18 at 11:42

Rafael Herscovici

14,724
15
61
91

answered Mar 30 '18 at 22:00

Usman Khan

562
1
5
14

score 6 · Answer 9 · edited Apr 01 '18 at 16:38

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

score 2 · Answer 10 · answered Feb 04 '20 at 09:42

public partial class recommended_books : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.RecommendedBook",
            c => new
                {
                    RecommendedBookID = c.Int(nullable: false, identity: true),
                    CourseID = c.Int(nullable: false),
                    DepartmentID = c.Int(nullable: false),
                    Title = c.String(),
                    Author = c.String(),
                    PublicationDate = c.DateTime(nullable: false),
                })
            .PrimaryKey(t => t.RecommendedBookID)
            .ForeignKey("dbo.Course", t => t.CourseID, cascadeDelete: false) // was true on migration
            .ForeignKey("dbo.Department", t => t.DepartmentID, cascadeDelete: false) // was true on migration
            .Index(t => t.CourseID)
            .Index(t => t.DepartmentID);

    }

    public override void Down()
    {
        DropForeignKey("dbo.RecommendedBook", "DepartmentID", "dbo.Department");
        DropForeignKey("dbo.RecommendedBook", "CourseID", "dbo.Course");
        DropIndex("dbo.RecommendedBook", new[] { "DepartmentID" });
        DropIndex("dbo.RecommendedBook", new[] { "CourseID" });
        DropTable("dbo.RecommendedBook");
    }
}

When your migration fails you are given a couple of options: 'Introducing FOREIGN KEY constraint 'FK_dbo.RecommendedBook_dbo.Department_DepartmentID' on table 'RecommendedBook' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.'

Here is an example of using the 'modify other FOREIGN KEY constraints' by setting 'cascadeDelete' to false in the migration file and then run 'update-database'.

score 1 · Answer 11 · answered Dec 07 '15 at 15:29

1

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.

answered Dec 07 '15 at 15:29

Marco Alves

2,645
3
21
33

score 1 · Answer 12 · answered Aug 27 '17 at 21:07

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.

This was it for me! Tried all variants of fluid configuration of cascading behaviour, but i kept seeing the same SQL beeing executed, beacuse of already created migrations that were trying to apply first :/ — BobbyTables, Aug 21 '20 at 08:06

CodingYourLife · Answer 13 · 2017-12-15T12:28:15.707

The existing answers are great I just wanted to add that I ran into this error because of a different reason. I wanted to create an Initial EF migration on an existing DB but I didn't use the -IgnoreChanges flag and applied the Update-Database command on an empty Database (also on the existing fails).

Instead I had to run this command when the current db structure is the current one:

Add-Migration Initial -IgnoreChanges

There is likely a real problem in the db structure but save the world one step at a time...

score 1 · Answer 14 · answered Oct 03 '19 at 05:20

The simple way is to, Edit your migration file (cascadeDelete: true) into (cascadeDelete: false) then after assign the Update-Database command in your Package Manager Console.if it's problem with your last migration then all right. Otherwise check your earlier migration history, copy those things, paste into your last migration file, after that do it the same thing. it perfectly works for me.

score 1 · Answer 15 · answered Apr 04 '20 at 19:29

1

Make your Foreign key attributes nullable. That will work.

answered Apr 04 '20 at 19:29

Umair Javed

21
1

1

that the answer in comments under questions please elaborate there – Kostia Mololkin Apr 05 '20 at 09:30

score 0 · Answer 16 · answered Feb 06 '18 at 15:29

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.

Imtiaz Zaman Nishith · Answer 17 · 2020-11-08T08:50:54.630

I ran into the same problem and stuck for a long. The following steps saved me. Go through the constraints and change the onDelete ReferentialAction to NoAction from Cascade

  constraints: table =>
  {
      table.PrimaryKey("PK_table1", x => x.Id);
      table.ForeignKey(
         name: "FK_table1_table2_table2Id",
         column: x => x.table2Id,
         principalTable: "table2",
         principalColumn: "Id",
         onDelete: ReferentialAction.NoAction);
  });

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

17 Answers17

Linked

Related