What is the disadvantages to using ON DELETE CASCADE on every foreign key?
My application currently has "soft delete" built-in to most tables with a BIT "deleted" column. There is now a requirement for "hard delete" functionality. Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database?
Why sql server cascade delete is bad? Should we use on delete cascade?
Why sql server cascade delete is bad? sql server cascade delete should not cause an unexpected loss of data. If a delete requires related records to be deleted, and the user needs to know that those records are going to go away, then cascading deletes should not be used. Should we use on delete cascade? Yes, the use of ON DELETE CASCADE is fine, but only when the dependent rows are really a logical extension of the row being deleted. For example, it's OK for DELETE ORDERS to delete the associated ORDER_LINES because clearly, you want to delete this order, which consists of a header and some lines. Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database?
Pros:
- When you delete a row from the Parent table all the foreign key rows are deleted
- This is usually faster than implementing this with triggers
- Orphaned rows are unlikely
Cons Orphans are possible If by mistake you delete a row in the parent table all the rows in the corresponding child tables will be deleted and it will be PITA to figure out what you deleted
Hope this will help!