Home > Software design >  Cascading deletes with multiple foreign keys
Cascading deletes with multiple foreign keys

Time:10-26

I'm writing an application where I'm using Entity Framework to handle my database.

My database design is shown here:

enter image description here

I would like to use cascading deletes, so when a customer is deleted in the "customer" table, the corresponding rows in the "rentals" table is deleted.

Vice versa whenever a product is deleted from the "products" table, the corresponding rows in rentals are deleted as well.

Is this possible?

If so, does Entity Framework set this up as automatically when cascading deletes are enabled? Or do I need to configure this manually?

CodePudding user response:

Is this possible?

Yes. How - later.

If so, does Entity Framework set this up as automatedly when cascading deletes are enabled?

No. SQL Server can not handle it using foreign key constraints - only one update / delete path is allowed. This is a serious limitation, but one that can be worked around, with a little code.

What you CAN do using a trigger. INSTEAD OF (instead of the delete, execute the trigger), and in the trigger you can delete both related tables and then delete the original data.

EF then works - any delete just triggers the trigger which results in the expected data being deleted.

  • Related