Cascaded deletes scare me anyway. It only takes one idiot to implement UPSERT as DELETE+INSERT because it seems easier, and child data is lost. You could always use triggers to cascade you soft-delete flags as an alternative method, though that would be less efficient (and more likely to be buggy) than the built-in solution that cascaded deletes are.
If you look at how system-versioned (or “temporal”) tables are implemented in some DBMSs, that is a good compromise. The history table is your audit, containing all old versions of rows even deleted ones, and the base table can be really deleted from, so you don't need views or other abstractions away from the base data to avoid accidentally resurrecting data. You can also apply different storage options to the archive data (compression/not, different indexes, ... depending on expected use cases) without more manaully setting up partitioning based on the deleted/not flag. It can make some query times less efficient (you need to union two tables to get the latest version of things including deleted ones, etc.) but they make other things easier (especially with the syntactic sugar like AS AT SYSTEM_TIME <when> and so forth) and yet more things are rendered possible (if inefficient) where they were not before.
> I tend to prefer a CDC stream into a separate historical system of record.
This is similar, though with system versioned tables you are pretty much always keeping the history/audit in the same DB.
---
FWIW: we create systems for highly regulated finance companies where really deleting things is often verboten, until it isn't and then you have the other extreme and need to absolutely purge information, so these things are often on my mind.
Seems unfortunate to miss out on all the referential integrity benefits of a serious database when hiring standards, training and code reviews should all be preventing idiotic changes.
If I’m making a shopping cart system, I want to know every order line belongs to an order, every order belongs to a user and so on. Anyone who can’t be trusted to write an update statement certainly can’t be trusted to avoid creating a bunch of orphan records IMHO.
If you don't use ON DELETE CASCADE, the actual foreign key constraint gives you a meaningful error-- that you need to delete some stuff to have referential integrity.
ON DELETE CASCADE --- you're telling it "eh, if you need to delete some stuff to avoid an error, go ahead, don't bother me, do what I mean".
You don't lose any referential integrity without cascades. Foreign keys are still enforced, just with an error if an action would break integrity rather than automatic deletes to satisfy the constraint that way.
> when hiring standards, training and code reviews should all be preventing idiotic changes
I was burned by this sort of thing early on, in companies where I had few such luxuries. Even though things are done better now, I'm still paranoid of that one day someone skips procedure and somehow lets a problem through all the QA loops.
> If I’m making a shopping cart system, I want to know every order line belongs to an order, every order belongs to a user and so on.
I take it from the other side: I want to know that if something is referred to elsewhere it can't be deleted until that is resolved.
If a top-level manager leaves I want an error if the hierarchy hasn't been updated before deleting his record¹, rather than his underlings, their underlings, their underling's underlings, … , being deleted when that one person is!
----
[1] Obviously this would normally be a soft-delete, there may be a lot of records referring to such an individual not just other person records. If you actually need to delete them (right to be forgotten etc.) then you need to purge the PII but keep the record so other things still hang together.
Mostly I use soft-delete because for auditing requirements we pretty much can't remove anything but also because nothing ever truly goes away. If we have an Invoice or Order then, from our perspective, we must have those forever even if the corresponding client is deleted and can never place another one.
Exactly. Unless you're doing something silly like adding deleted at to bridge tables ... which, you probably don't need even in 1:many.
Right now in $dayjob I am converting an old non-DRY codebase from NoSQL data layer format to proper relational SQL backend.
This old front-end was created by verbosely coding up a relational cascading update/delete system for the NoSQL backend, in numerous places redundantly with subtle differences and inconsistencies, making the code brittle.
My current estimate is some front end functions will be reduced in LOC size by 95% once we use the power of SQL in the backend.
And the backend SQL Triggers+StoredProcedure required to replace these long NoSQL frontend functions doing cascading updates/deletes is only around 10% the size of the replaced front-end code.
And now future new frontends can reuse this without exploding the size of their codebase where complex data operations are required. And no need to reinvent the same data handling algorithm all over again (and risk subtle variation creeping in from the different front-end implementation of data algorithms)
If you're doing everything as one big table of entity-attribute-value with generic blobs for the values, then yes you'll have to re-implement all the normal constraints (that the database would handle) in your application and do all your data integrity handling there. And you'll also have to duplicate that logic across every application that accesses that database now and in the future.
Data usually lives longer and has more uses than just one program. So I think it's generally better to put integrity constraints in the database, rather than having to re-implement and duplicate that logic several places.
The information schema table holds all foreign key relationships, so one can write a generic procedure that cascades through the fkey graph to soft-delete rows in any related tables.
It's not a standard (I think) but it'd let you do a cascading delete and then be able to go and look at the old objects as they were at time of deletion too.
You'd need to do things very differently to show a list of deleted objects though.
They were introduced in ANSI SQL 2011.
How closely implementations follow the standard I don't know, but something close exists in several DBMSs: I use them regualrly in MS SQL Server, there are plug-ins for postgres, MariaDB has them, and so forth.
However in practice this usually dramatically slows down reads if you have to constantly skip over the historic rows so you probably don't want to keep garbage round longer than absolutely necessary. The concept of a historic table mentioned below could be interesting though - especially if it could be offloaded to cold storage.
If you use soft deletes on all tables, you can also cascade them as long as you either cascade updates to the real keys as well, or prevent such updates, by having a deleted flag column on each table, including it in a unique constraint with the actual key column(s), and including it in the foreign key.
This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships, I need to re-defined these relationship between objects at the application layer. This gets more and more difficult as relationships between objects increase.
If the goal is to keep a history of all records for compliance reasons or "just in case", I tend to prefer a CDC stream into a separate historical system of record.