Our 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?
Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything.
A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the foreign key with the cascade.
If you're going to delete cascade everything, you'll almost definitely hit this problem and either have to rework relations or be selective in which foreigns key actually do get cascades and which ones don't.
Explanation with illustrated example (not my source) Link
One way to get around it is to either use procedures/'front end' code (do the delete order management in the application calling the DB), or use triggers. Just try to be consistent with it. If your data model allows you to not hit multiple cascade paths, and you're certain you don't mind the 'oops'. Then by all means use it.
If you're going to hit multiple cascade paths, my advice would be to make the implementated solution consistent. Having some cascade, some triggers, some through procedures doing the management. Just makes the code disorganized.
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?
One reason not to : Oops!!
If you accidentally delete the wrong record, then you wipe out everything that references on that record. If that record happens to be the one at the root of your entire company, then everything goes "Poof!", everybody starts screaming at you and you're into a major, high-profile, high-pressure Recovery exercise.
Personally, I advocate never cascading deletes.
You want to get rid of data? You need to know what you're doing! (Or some, centrally managed, piece of code needs to know how to do so on your behalf).
ON UPDATE CASCADE is generally safe (though can be very inefficient) if the parent value is unique and not nullable, for instance a primary yet. If it is both unique and not nullable then there is nothing the cascade can do that can not be undone if it was a mistake.
ON DELETE CASCADE is something I consider pretty dangerous so generally avoid. Needing to delete children first can be a hassle but you don't want to make it easy to permanently remove something IMO. As well as generally accidents where something is removed before its children, I've many times seen UPSERT implemented with DELETE-then-re-INSERT: this means you don't have to check what exists and issue UPDATE and INSERT accordingly (or use MERGE which has its own concerns). If someone tries this on an entity that has children but no cascade, then the operation will simply fail: you can't delete the parents and if there is no transaction or error handling so the insert tries to happen anyway that'll fail due to key conflicts (caveat: both these points assume you have appropriate primary/foreign/unique keys defined). If someone does this with cascading turned on, the delete will work and take out the child rows too automatically but the insert will obviously not put them back along with the new parent row so data is silently lost.
In the Stack Overflow question, What are the Pros and Cons of Cascading delete and updates?
The accepted answer by SQLMenace states:
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
It depends primarily on your business rules, but in general I like to use cascading deletes sparingly because my philosophy is that a database should document facts and be a lasting record. My opinion is definitely biased because I deal with databases where there is a requirement to answer the question "what did we know when." Deletions are rare events and we definitely do not want a delete to happen if there are any dependent records.
OTOH, if you are a social media company and you wanted to have a user-friendly data retention policy, you would want cascading deletes when a user deletes their account.
From a performance point of view, if you have a large database (where large is relative to your server's capabilities), you may want to be more deliberate in how deletes happen.
From a gotcha point of view, cascading deletes can be like the "rm -rf /" mistake. To ensure data integrity, you may want to prohibit a delete if there any dependent records. You may have a business requirement to document what data is being deleted and why. Such requirements may exist for statutory or regulatory purposes, so there may be legal ramifications if data is deleted when it was not supposed to be deleted.
