Does that pattern you describe require any considerations when writing code? I’m thinking of applications I’ve worked on where events are triggered by change, and so a database rolling back independent of my application would be a nightmare. I treat the database as a place to store data, not an authority: the application is the authority. Do you approach it differently? Thanks!
The database is the only place that can be the authority because the application can have race conditions. It’s the only way to guarantee data integrity.
There's no way to specify every single application specific constraint directly in the database. Race conditions are not present when using locking reads (select ... for update, or DB specific shared locking selects) or serializable isolation level, which are the typical way of enforcing application level constraints.
ON DELETE CASCADE can be dangerous when used with applications that expect to be notified of deletions, like in your case.
Ideally, everything that needs to change when a row is deleted would be changed automatically and atomically using database-side constraints and triggers. In practice, applications often need to sync state with external services that the database knows nothing about, so I understand your concerns.
ON DELETE RESTRICT, on the other hand, will result in errors just like any other query error that you can handle in your application. Nothing happened, so there's nothing to be notified of.
