What Is the Problem with Foreign Key Cascade Multiple Paths and Cycles

What is the problem with foreign key cascade multiple paths and cycles?

You have a child table with 2 cascade paths from the same parent: one "delete", one "null".

What takes precedence? What do you expect afterwards? etc

Note: A trigger is code and can add some intelligence or conditions to a cascade.

How can these constraints may cause cycles or multiple cascade paths?

The issue is due to multiple cascading paths from Author (Grand Parent) to BookRelease (Grand Child). There are two cascading paths:

1. Author -> Book -> BookRelease
2. Author -> Nickname -> BookRelease

This is discussed in detail in the post in MSSQLTips

So, the way to handle this is:

1. Disable the ON DELETE CASCAE and choose NOACTION as the foreign key creation.

2. Create INSTEAD OF DELETE TRIGGERS in Author(GrandParent), Book(Child1), Nickname(Child2) tables to handle the deletion of parent keys in the child tables.

• Grand Parent deletion : Delete in GrandChild, followed by Child1,
  followed by Child2,
• Child1 deletion: Delete in GrandChild, followed
  by Child1
• Child2 deletion: Delete in GrandChild, followed by Child2

FK constraints may cause cycles or multiple cascade paths

The relational diagram clearly shows the multiple cascade path from ProductType to PropertyOptionForProduct:

(1) ProductType -> Product -> PropertyOptionForProduct

(2) ProductType -> ProductProperty -> ProductPropertyOption -> PropertyOptionForProduct

The only solution is to break the cascade path by turning off the cascade delete for at least one of the relationships and then handle the principal entity deletion manually.

Probably the easiest is to break some of the root paths, for instance ProductType -> ProductProperty:

modelBuilder.Entity<ProductType>()
    .HasMany(e => e.Properties)
    .WithOne(e => e.ProductType)
    .OnDelete(DeleteBehavior.Restrict);

Then when you need to delete a ProductType, instead of the "normal":

db.Remove(db.Set<ProductType>().Single(e => e.Id == id));
db.SaveChanges();

you have to first delete the related Properties:

var productType = db.Set<ProductType>().Include(e => e.Properties).Single(e => e.Id == id);
db.RemoveRange(productType.Properties);
db.Remove(productType);
db.SaveChanges();

Foreign key constraint may cause cycles or multiple cascade paths?

SQL Server does simple counting of cascade paths and, rather than trying to work out whether any cycles actually exist, it assumes the worst and refuses to create the referential actions (CASCADE): you can and should still create the constraints without the referential actions. If you can't alter your design (or doing so would compromise things) then you should consider using triggers as a last resort.

FWIW resolving cascade paths is a complex problem. Other SQL products will simply ignore the problem and allow you to create cycles, in which case it will be a race to see which will overwrite the value last, probably to the ignorance of the designer (e.g. ACE/Jet does this). I understand some SQL products will attempt to resolve simple cases. Fact remains, SQL Server doesn't even try, plays it ultra safe by disallowing more than one path and at least it tells you so.

Microsoft themselves advises the use of triggers instead of FK constraints.

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

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

Introducing FOREIGN KEY may cause cycles or multiple cascade paths

I almost sure I do not have cycles

You do have a cycle from customers to payments. If you delete a customer, then the payment can be deleted via customers->payments or customers->billingCenters->invoices->payments, hence the cycle.

and I would not have problem if I had cascade path. It's what I would like to have instead!

I'm sure that's exactly what we want. In fact some databases (Oracle for sure) have no problems with multiple cascade paths. Unfortunately SqlServer does not support them, so EF designers decided to not support such configuration.

I say that it SEEMS to work because if I see the relation between billingCenters and invoices the delete rule is no action.

This is because your migration has failed and has been rolled back. It really tries to set delete cascade.

How Can I solve the problem?

You are expected to break the cycle. You can do that by turning off cascade delete (by including WillCascadeOnDelete(false) in the respective relationship configuration) for at least one of the relationships customers->payments or customers->billingCenters.

Doing that will "solve" the issue. I mean you'll be able to run successfully the migration. But note that you might have a maintenance problems - depending of how database processes the FK constraints, it might be impossible to simply delete a customer and require manually delete the related records (payments or billingCenters) before deleting it.

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify on delete no action

I have fixed this problem

The problem occurs because I have two cascading delete path to the CandidateJobMap table:

If I delete employer, its going to delete related employer jobs which will in turn delete CandidateJobMap table:

Employer->Jobs->CandidateJobMap

If I delete candidate, its going to delete CandidateJobMap table:

Member->CandidateJobMap

So to get around this problem, I have to disable one of the delete path, you cannot specify WillCascadeDelete(false) when you are creating many to many relations, so instead you have to change migration as follows:

CreateTable(
   "dbo.MemberJobMap",
   c => new
   {
      Id = c.String(nullable: false, maxLength: 128),
      JobId = c.Int(nullable: false),
   })
   .PrimaryKey(t => new { t.Id, t.JobId })
   .ForeignKey("dbo.Members", t => t.Id, cascadeDelete: false) <--------cascade delete to false
   .ForeignKey("dbo.Jobs", t => t.JobId, cascadeDelete: true)
   .Index(t => t.Id)
   .Index(t => t.JobId);   

Now because you set cascade delete to false, when a candidate has been deleted, it won't delete related CandidateJobMap rows, this will cause another error when you try to delete a candidate where it is also a related key in CandidateJobMap, so you have to manually delete related rows in CandidateJobMap before removing the candidate:

//remove all applied jobs from user, without doing this, you will receive an error
foreach (var appliedjob in user.Member.Jobs.ToList())
{
    user.Member.Jobs.Remove(appliedjob);
}

//before you can delete the user
await UserManager.DeleteAsync(user);

Not sure if this is the best way, but it worked for me.

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

All relationships in your model are required because all foreign key properties (CountryId, RegionId, CityId) are not nullable. For required one-to-many relationships EF will enable cascading delete by convention.

Country and Region have multiple delete paths to the Store table, for example if you delete a Country the related Stores can be deleted via three different cascading paths (which is not allowed with SQL Server):

• Country -> Store
• Country -> Region -> Store
• Country -> Region -> City -> Store

You must avoid such ambiguous delete paths by either disabling cascading delete using Fluent API or by defining some of the relationships as optional (with a nullable foreign key Guid?).

Or remove the Stores collections (and the inverse references and FK properties) from all entities except City. To me those collections look redundant because you can find all stores in a Country by navigating through the Regions.Cities.Stores collections.

Avoiding 'Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths'

It is unfortunate default behavior of EF code first to crate FK with on delete cascade. So while defining relations you need to simply change this setting:

//in context
protected override void OnModelCreating(DbModelBuilder modelBuilder) { 
    modelBuilder.Entity<Project>()
        .HasRequired<User>(s => s.User)
        .WithMany()
        .WillCascadeOnDelete(false);

Also there might be a convention for it in modelBuilder.Conventions if you want to just change behavior for all FK.

---

Related Topics