4 ms·
Deleting in a relational database often involves cascading to foreign keys to ensure data integrity is maintained. Delete a company row, and the employee rows m
by mickeyp 4y ago
Deleting in a relational database often involves cascading to foreign keys to ensure data integrity is maintained. Delete a company row, and the employee rows may follow; that is probably not what most want.
FK cascading and DELETE, when done poorly, can... well, cascade, and delete lots of things the end user did not foresee. Like nuking half the database levels of bad.
`deleted_at' flags avoid that problem; combine it with row-level security in your DB (like postgres) and you can hide the rows permanently as well: from orms and non-superuser roles alike with a simple policy not to show any row where `deleted_at` is not null.
- silisili 4y agoI like cascading, but understand that risk. Why not just restrict everything, then? It forces the code to specify everything it wants to delete, no hidden nuking...
- noisy_boy 4y agoUse FK etc to ensure referential integrity but don't use it for cascading deletes. Speaking of Finance industry, stuff gets seldom deleted and when it does, more often than not, it is either manually orchestrated or the same orchestration is done via code. Sure it's more work but you are in full control of what is being removed at each stage.
- deleted 4y ago[deleted]