EF Core relationship throwing error when creating the database

ef-core-2.0 entity-framework-core

Question

I have an Address class, that I use in a Customer class and in an Order class:

public class Address
{
    public Customer Customer { get; set }
    ...
}

public class Customer
{
    ...
    public List<Address> Addresses { get; set;}
}

public class Order
{
    ...
    public Customer Curstomer { get; set; }
    public Address BillingAddress { get; set;}
    public Address ShippingAddress { get; set;}
}

I created the migrations succesfully but when I try to update-database I get the following error:

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

What is the correct way of setting up such relationship? Is this modelling even correct? Because it seems strange to me that Address is a property related to Customer, but I'm also using it on Order, but duplicating the addresses in an OrderAddresses table seems wrong too.

1
0
8/21/2019 6:46:56 PM

Accepted Answer

To fix this, change the ReferentialAction for the onDelete of your foreign key in the migration to something other than Cascade. Restrict is probably a good option. It will look something like this:

constraints: table =>
            {
                table.PrimaryKey("PK_Order", x => x.Id);
                table.ForeignKey(
                    name: "FK_Orders_BillingAddress_BillingAddressId",
                    column: x => x.BillingAddressId,
                    principalTable: "Addresses",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            }

Just change that to Restrict or another choice.

Why?

If you have Cascade, suppose you were to delete the BillingAddress of your Order. That would try to cascade the delete to the Order which would then cascade its delete to the ShippingAddress which would then try to cascade that delete back to the Order and so on and so forth, hence why SQL Server correctly errors out on cyclical cascading deletes.

See also this question: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

3
4/18/2018 5:05:57 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow