I'm guessing it's the possibility for data loss. So someone accidentally deletes a user and it cascade deletes all invoices referenced to that user (deleting invoices is a big no-no in accounting).

These kinds of things are exactly why foreign keys are so helpful to keep things from getting corrupted. Invoices should always reference a user in the user table.

The relationship should not be "ON DELETE CASCADE" but probably "ON DELETE NO ACTION". Then when some junior dev tries to issue a DELETE on the users table, the DB will correctly tell them to get bent and throw an error because there are dangling invoices still associated with the user. Because you are a smart person, you've also prohibited DELETE and UPDATE's against the invoices table for the db user that is used. The application cannot delete the invoice, and it cannot delete the user either--which is the only correct course of action. The database can now protect itself from hostile input from all your junior devs. Exactly why you want FK's. You can't trust user input.

The fact that the author of the article shudders about cascading deletes demonstrates that they have zero authority to speak about databases--something that is very common in the developer community. DB's aren't mysterious black boxes. They are really fucking cool tools that can do all kinds of neat shit to keep your data nice, safe, cozy and warm (they do way more than that too... a good database is perhaps one of the most underutilized tools developers have in their toolbox)

If the author is thinking of that kind of situation, he's right that ON DELETE CASCADE would be inappropriate, but he's also incredibly wrong to not want a foreign key enforcing that relationship. Deleting a user associated with invoices that should not be deleted is a big no-no too. A foreign key would not let the user be deleted until after the associated invoices are deleted.

There are plenty of other situations where ON DELETE CASCADE is the best option.

the problem with ON DELETE CASCADE isn't the CASCADE, it's the DELETE. Almost always, you want to to mark an entity disbled, not really DELETE it. If you want to DELETE it (for GDPR?) you should have something in place to fail your delete unless you've properly defined how to clean up danglig keys. (Perhaps what you need to do is delete the non-primary-key fields containing user data, but keep the row for relational integrity)

ON CASCADE DELETE is used for deleting logical sub-components (a row owned by another row) in a well normalized database -- it's for deleting your dadress book when you delete your account. It's not for deleting all your friends when your account is deleted.

> It's not for deleting all your friends when your account is deleted.

Very nice summary up until that point. But in a well normalized db the friends relationship (many-to-many) would be its own table. Friend relationships with the deleted user would be deleted, as they should be. Friends would not. (You still have time to edit for a better example.)

Okay, then don't do ON DELETE CASCADE. If you really want to allow the user to be deleted entirely, then ON DELETE SET NULL (or ON DELETE SET DEFAULT pointing to the ID of some "null" user) should do the trick. Otherwise, if you want to retain the customer info (which if you're saving the invoices you almost certainly do), then ON DELETE RESTRICT is what you want. This is all defined in the context of the invoice's schema, mind you, so you'd have to make a conscious effort to cascadedly delete your invoices on user deletion when defining that foreign key constraint.

Use the database to do your bidding. Easier to stay sane that way :)

I mean, sure, don't use on delete cascade in that case. But there's a big difference between "sometimes bad" and "always bad". The link seems to argue "always bad".

If I have data which much always be deleted if its owner is deleted, what's the process? Manually issue exhaustive delete statements in a transaction?

Is on delete cascade always bad or just a bad default? (It's obviously a bad default, I'm not arguing that.)