3 ms·

On that note, has anybody figured out a nice way to combine foreign keys and soft deletion (that is, a deleted_at column)? Soft deletion is occasionally useful

On that note, has anybody figured out a nice way to combine foreign keys and soft deletion (that is, a deleted_at column)? Soft deletion is occasionally useful, but losing foreign keys for it is a big pain. EDIT: got a bunch of responses, thanks! To be clear, the issue I have in mind is e.g. you want to have a foreign key that makes sure the “singular” side of a one-to-many relationship isn’t soft-deleted (on delete restrict) as long as it has anything in the “many” side. Both sides using soft-deletion.

michaelmior 3y ago
I see at least two possible approaches (each with their tradeoffs, neither great): 1. For each relevant foreign key, have an additional column without a constraint where the foreign key value can be copied to before deletion. (This requires that the original foreign key be nullable.) 2. Make soft deletion universal so that foreign keys can remain and any associated rows will still exist.
stop50 3y ago
How are foreign keys and deleted_at mutual exclusive?
- dspillett 3y ago
  I assumed cube2222 meant where soft-delete wasn't universal: so you might really delete the parent row but want to soft-delete the child rows. Though I would suggest this is a bad idea: if you want to soft-delete child rows you should enable it on parent rows too.
- cube2222 3y ago
  E.g. you want to have a foreign key that makes sure the “singular” side of a one-to-many relationship isn’t soft-deleted as long as it has anything in the “many” side. Both sides using soft-deletion.
PeterisP 3y ago
Just brainstorming - perhaps one way to do that would be a multi-column FK towards the row ID combined with an is_deleted column, which would mean that the FK constraint also enforces updating the related rows if something is deleted.
arrowleaf 3y ago
By soft deletion, do you mean you would want to ever re-instate the record and make it valid again, PK/FK constraints and all? Or tracking deleted records? For the latter I use audit tables + triggers to track the changing values. At a basic level you could duplicate the entire record into the audit table on every action, e.g. the audit table would look like `audit_id | record_id | user/process_id | action (insert, update, delete) | timestamp | ...<record rows>`. You can optimize it to not duplicating column values unless necessary. On inserts you only need the metadata of the action. On updates, the old value of columns with changes goes into the audit table. On deletes the whole record goes into the audit table.
yen223 3y ago
The `deleted_at` column approach to soft deletion has enough downsides that I would choose to just move "deleted" records into a different table. Having a separate table for deleted records means that - FK references to the main table will just work - the main table can be kept clean. - it avoids the class of problems where a user or the application treats soft-deleted records as real records, because they weren't aware of the `deleted_at` column.

michaelmior 3y ago

I see at least two possible approaches (each with their tradeoffs, neither great): 1. For each relevant foreign key, have an additional column without a constraint where the foreign key value can be copied to before deletion. (This requires that the original foreign key be nullable.) 2. Make soft deletion universal so that foreign keys can remain and any associated rows will still exist.

stop50 3y ago

How are foreign keys and deleted_at mutual exclusive?

dspillett 3y ago
I assumed cube2222 meant where soft-delete wasn't universal: so you might really delete the parent row but want to soft-delete the child rows. Though I would suggest this is a bad idea: if you want to soft-delete child rows you should enable it on parent rows too.
cube2222 3y ago
E.g. you want to have a foreign key that makes sure the “singular” side of a one-to-many relationship isn’t soft-deleted as long as it has anything in the “many” side. Both sides using soft-deletion.

PeterisP 3y ago

Just brainstorming - perhaps one way to do that would be a multi-column FK towards the row ID combined with an is_deleted column, which would mean that the FK constraint also enforces updating the related rows if something is deleted.

arrowleaf 3y ago

By soft deletion, do you mean you would want to ever re-instate the record and make it valid again, PK/FK constraints and all? Or tracking deleted records? For the latter I use audit tables + triggers to track the changing values. At a basic level you could duplicate the entire record into the audit table on every action, e.g. the audit table would look like `audit_id | record_id | user/process_id | action (insert, update, delete) | timestamp | ...<record rows>`. You can optimize it to not duplicating column values unless necessary. On inserts you only need the metadata of the action. On updates, the old value of columns with changes goes into the audit table. On deletes the whole record goes into the audit table.

yen223 3y ago

The `deleted_at` column approach to soft deletion has enough downsides that I would choose to just move "deleted" records into a different table. Having a separate table for deleted records means that - FK references to the main table will just work - the main table can be kept clean. - it avoids the class of problems where a user or the application treats soft-deleted records as real records, because they weren't aware of the `deleted_at` column.