FK constraints may cause cycles or multiple cascade paths

Question

Why is my initial update-database failing, and what do I need to change in my db table class(es) to make it work?

Sure, I can change the onDelete: ReferentialAction.Cascade in the migration script to onDelete: ReferentialAction.NoAction, but then I will face other problems in my application. I'm after a solution where there's no need to edit the migration script generated by add-migration. In other words, I'm open to make changes to my database schema.

The behaviour I want is that when I delete a Product, the associated ProductPropertyOptionForProducts is also deleted, but not the other way around, and not the ProductPropertyOption which is associated with the ProductPropertyOptionForProducts.

This is the migration output error message:

Introducing FOREIGN KEY constraint 'FK_PropertyOptionsForProducts_ProductPropertyOptions_ProductPropertyOptionId' on table 'PropertyOptionsForProducts' 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.

The generated SQL command which caused the error:

CREATE TABLE[PropertyOptionsForProducts] (
[Id] int NOT NULL IDENTITY,
[CustomNumberValue] decimal (18, 2) NOT NULL,
[CustomRangeFrom] decimal (18, 2) NOT NULL,
[CustomRangeTo] decimal (18, 2) NOT NULL,
[CustomStringValue] nvarchar(max) NULL,
[ProductId] int NOT NULL,
[ProductPropertyId] int NOT NULL,
[ProductPropertyOptionId] int NOT NULL,
CONSTRAINT[PK_PropertyOptionsForProducts] PRIMARY KEY([Id]),
CONSTRAINT[FK_PropertyOptionsForProducts_Products_ProductId]
    FOREIGN KEY([ProductId])
    REFERENCES[Products] ([Id]) ON DELETE CASCADE,
CONSTRAINT[FK_PropertyOptionsForProducts_ProductPropertyOptions_ProductPropertyOptionId]
    FOREIGN KEY([ProductPropertyOptionId])
    REFERENCES[ProductPropertyOptions] ([Id]) ON DELETE CASCADE
);

The classes:

public class ProductPropertyOption
{
    public int Id { get; set; }
    public int ProductPropertyId { get; set; }
    // some more properties
    public ProductProperty Property { get; set; }
    public ICollection<PropertyOptionForProduct> PropertyOptionForProducts { get; set; }
}


public class PropertyOptionForProduct
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int ProductPropertyId { get; set; }
    public int ProductPropertyOptionId { get; set; }
    // some more properties
    public Product Product { get; set; }
    public ProductPropertyOption ProductPropertyOption { get; set; }
}


public class Product
{
    public int Id { get; set; }
    public bool Published { get; set; }
    public int ProductGroupId { get; set; }
    public int ProductGroupSortOrder { get; set; }
    // some more properties
    public int ProductTypeId { get; set; }

    public ICollection<ProductImage> Images { get; set; }
    public ICollection<PropertyOptionForProduct> ProductPropertyOptionForProducts { get; set; }
    public ICollection<IdentifierForProduct> IdentifierForProducts { get; set; }
    public ProductType Type { get; set; }
    public ICollection<FrontPageProduct> InFrontPages { get; set; }
    public ICollection<ProductInCategory> InCategories { get; set; }
}


public class ProductType
{
    public int Id { get; set; }
    public string Title { get; set; }
    public List<ProductIdentifierInType> Identifiers { get; set; }
    public List<ProductProperty> Properties { get; set; }
    public ICollection<Product> Products { get; set; }
}


public class ProductProperty
{
    public int Id { get; set; }
    public int ProductTypeId { get; set; }
    // some more properties
    public List<ProductPropertyOption> Options { get; set; }
    public ProductType ProductType { get; set; }
}

The database (the Products and categories-part) illustrated:

Answer 1

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

Answer 2

The "cascade" action was, afaik, included in the very first designs of relational databases. In the beginning, it was seen as a convenient way to control the possibility of orphaned records. And it was...at first.

As these databases grew larger and larger, however, the Cascade caused more problems then they were worth...as you have seen.

One solution is to create views which expands all the direct relationships. An "instead of" trigger on the view will handle deletion of the dependent entities before deleting the target entity.

For example, the view "ProductTypeForDelete" could look like this:

select * from ProductTypeForDelete where ID = 1001;
ID    TABLE              KEY
===== ==========         =====
1001  Product            300
1001  Product            301
1001  ProductProperty    203

Consider the command:

delete from ProductTypeForDelete where ID = 1001;

The trigger would receive the result set shown above. It shows 2 dependencies in the Product table and one in the ProductProperty table. So the delete trigger on the view knows it needs to delete from those two tables before deleting from the ProductType table.

There would also be views ProductForDelete and ProductPropertyForDelete that would continue the chain. The delete trigger on the view PropertyOptionForProductForDelete would know it was at the end of the chain and just perform the delete. Then the chain of execution would unwind, deleting from their target table as they go.

You may think that would be a lot of views and a lot of triggers, but it is all just code and amazingly easy to maintain. Another advantage is this works when deleting from anywhere in the relationship chain. To delete a product instead of the entire type of product, just issue the command:

delete from ProductForDelete where ID = 300;

and everything works as expected.

Aren't we just emulating the "cascade" feature? No, there is a very important difference. If you've defined all the tables with cascade delete, deleting from the ProductType table will lock that table, then lock the Product and ProductProperty tables and on down the line. Every table in every relationship branch must be locked before any deleting can be performed. Using views, the locking is performed first at the end of the chain, the deletion performed, the lock released and then the next table up is locked. This is exactly the behavior you want.