Hacker News Reader

Comment by cube2222 | Hacker News Reader

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.

View on HN

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.

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.

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.

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.

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.

How are foreign keys and deleted_at mutual exclusive?

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.

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.

Reply on news.ycombinator.com