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 lt Side gt             Stage   Stage ONE              Key       Required      public virtual int CardId   get  set          Required      public virtual Stage Stage   get  set          Required       ForeignKey  CardId        public virtual ICollection lt Side gt  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 lt Stage gt  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

User · Answer

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 lt OneToManyCascadeDeleteConvention gt

User · Answer

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 lt Card gt         HasRequired c   gt  c Stage       WithMany        WillCascadeOnDelete false    modelBuilder Entity lt Side gt         HasRequired s   gt  s Stage       WithMany        WillCascadeOnDelete false

User · Answer

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

User · Answer

public partial class recommended books   DbMigration       public override void Up                 CreateTable               dbo RecommendedBook               c   gt  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   gt  t RecommendedBookID               ForeignKey  dbo Course   t   gt  t CourseID  cascadeDelete  false     was true on migration              ForeignKey  dbo Department   t   gt  t DepartmentID  cascadeDelete  false     was true on migration              Index t   gt  t CourseID               Index t   gt  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

User · Answer

Anybody wondering how to do it in EF core         protected override void OnModelCreating ModelBuilder modelBuilder                                foreach  var relationship in modelBuilder Model GetEntityTypes   SelectMany e   gt  e GetForeignKeys                                           relationship DeleteBehavior   DeleteBehavior Restrict                                     rest of the code

User · Answer

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

User · Answer

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

User · Answer

I fixed this  When you add the migration  in the Up   method there will be a line like this    ForeignKey  dbo Members   t   gt  t MemberId  cascadeDelete True    If you just delete the cascadeDelete from the end it will work

User · Answer

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

User · Answer

Make your Foreign key attributes nullable  That will work

User · Answer

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 lt ManyToManyCascadeDeleteConvention gt     builder Conventions Remove lt OneToManyCascadeDeleteConvention gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt            table PrimaryKey  quot PK table1 quot   x   gt  x Id         table ForeignKey           name   quot FK table1 table2 table2Id quot            column  x   gt  x table2Id           principalTable   quot table2 quot            principalColumn   quot Id quot            onDelete  ReferentialAction NoAction

User · Answer

In  NET Core I changed the onDelete option to ReferencialAction NoAction           constraints  table   gt                                table PrimaryKey  PK Schedule   x   gt  x Id                   table ForeignKey                      name   FK Schedule Teams HomeId                       column  x   gt  x HomeId                      principalTable   Teams                       principalColumn   Id                       onDelete  ReferentialAction NoAction                   table ForeignKey                      name   FK Schedule Teams VisitorId                       column  x   gt  x VisitorId                      principalTable   Teams                       principalColumn   Id                       onDelete  ReferentialAction NoAction

User · Answer

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

[.net] Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

Examples related to .net

Examples related to entity-framework

Examples related to foreign-keys

Examples related to ef-code-first

Examples related to entity-framework-4