find a location for property in a new city

Friday, 17 May 2013

How to solve Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

I was writing my Entity Framework 5 Code First data models one day when I received a dramatic sounding error: "Introducing FOREIGN KEY constraint 'FK_dbo.Days_dbo.Weeks_WeekID' on table 'Days' may cause cycles or multiple cascade paths." I was instructed to "Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints." And then simply the message, "Could not create constraint." So what happened with my Foreign Key that makes it cyclic?"

First a spot of code that can nicely demonstrate this scenario:

public class Year
{
    public int ID { get; set; }
    public string Name { get; set; }

    public ICollection<Month> Months { get; set; }
}
public class Month
{
    public int ID { get; set; }
    public string Name { get; set; }

    public Year Year { get; set; }
    public ICollection<Day> Days { get; set; }
}
public class Day
{
    public int ID { get; set; }
    public string Name { get; set; }

    public Month Month { get; set; }
    public Week Week { get; set; }
}

//problem time
public class Week
{
    public int ID { get; set; }
    public string Name { get; set; }

    public Year Year { get; set; }
    public ICollection<Day> Days { get; set; }
}

So, let's explain this. A Year has Months and a Month has Days - all is well at this point and it will build and generate all your tables happily. The problem comes when you add the highlighted parts to add Weeks into the data model.

By making those Navigation Properties you have implicitly instructed EF Code First to create Foreign Key constraints for you and each of these will have cascading deletes on them. This means, upon deleting a Year, the database will cascade that delete to that Year's Months and in turn those Month's Days. This is to, quite rightly, hold referential integrity; you will not have Yearless Months.

So if you think about it, now that we have added a Week entity that also has a relationship with Days this too will cascade deletes. So as well as the cascading deletes that occur when you delete a Year as I described above, now deleting a Year will also delete its Weeks and those Weeks will delete the Days. But those Days would have already been deleted by the cascade that went via Months. So who wins? Who gets there first? Don't know... that's why you need to design an answer to this conundrum.

Removing the multiple cascade paths

The way I found to solve this issue is you need to remove one of the cascades, since there is nothing wrong with the Foreign Keys, it is only the multiple cascade paths. So lets remove the cascading delete on the Week -> Days relationship since that delete will be taken care of by each Month cascading its deletes to their Days.

public class CalenderContext : DbContext
{
    public DbSet<Year> Years{ get; set; }
    public DbSet<Month> Months { get; set; }
    public DbSet<Day> Days { get; set; }
    public DbSet<Week> Weeks { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Day>()
            .HasRequired(d => d.Week)
            .WithMany(w => w.Days)
            .WillCascadeOnDelete(false);

        base.OnModelCreating(modelBuilder);
    }
}

If you already have a ForeignKey field explicitly written into your code model (as I usually do - I removed them from the above example for clarity) you will need to specify this existing Foreign Key in the code just written. So if your Day entity looked like this:

public class Day
{
    public int ID { get; set; }
    public string Name { get; set; }

    [ForeignKey("Month")]
    public int MonthID { get; set; }
    public Month Month { get; set; }

    [ForeignKey("Week")]
    public int WeekID { get; set; }
    public Week Week { get; set; }
}

You will need your OnModelCreating method to look like this:

modelBuilder.Entity<Day>()
    .HasRequired(d => d.Week)
    .WithMany(w => w.Days)
    .HasForeignKey(d => d.WeekID)
    .WillCascadeOnDelete(false);

Have fun deleting safely!

Follow britishdev on Twitter

14 comments:

  1. Well I am so excited that I have found your post because I have been searching for some information on this for almost three hours! You've helped me a lot indeed and by reading this article I have found many new and useful information about this subject!
    Tungsten wedding band

    ReplyDelete
    Replies
    1. When made a foreign key in your facts and figures Base you should make sure that you are utilizing the right command while injecting or updating the data. Visit-http://www.escort-hk.net/.

      Delete
  2. Really like your blog content the way you put up the things…I’ve read the topic with great interest and definitely will stick your blog routinely for other great posts.
    skinny fiber

    ReplyDelete
  3. When made a foreign key in your Data Base you must make sure that you are using the right command while inserting or updating the data.|Dissertation Writers online||Purchase Dissertation|

    ReplyDelete
  4. I am really glad to find this key at here. I am really thankful to you for sharing such an informative things on your blog.hr assignment UK , assignment help by assignment cube.

    ReplyDelete
  5. The function keys material is to given by writer it is good to see that codding because before that I cant see that type of functional keys. write my dissertation || order dissertation

    ReplyDelete
  6. Legend has foretold that one day, someone will post a comment that is a complete sentence.

    ReplyDelete
  7. Great service, great prices, very conscientious. I'd recommend without hesitation. (but you might should consider spending the extra bucks on a Brompton rather than a Dahon if you want really fast folding ;-). electric bikes nz

    ReplyDelete
  8. That is very interesting information, I'm glad to be sharing your read.
    Yepi 8 | Yepi 6 | Hopy 4

    ReplyDelete
  9. If you want help of a dissertation writer, So you can choose dissertation writers on awesommessay.com

    ReplyDelete
  10. I really appreciate the quality of your work here. Thanks so much for sharing such a valuable information with the rest of the world.
    dissertation writing services|UK dissertation services|dissertation writing uk

    ReplyDelete
  11. Hi. I wanted to drop you a quick note to express my thanks. I have been following your blog for a month or so and have picked up a ton of good information as well as enjoyed the way you structured your site.
    Custom Essay Writing Services
    Assignment Writing

    ReplyDelete
  12. Thanks for sharing your programming views which will improve my Dissertation Writing Services UK and Dissertation Writing Services skills in its best way and making its quality best.

    ReplyDelete
  13. Nice article and I admire your work. Your blog was so informative and having good knowledge. Thanks for your post.
    dissertation writing expert

    ReplyDelete