In a schema that soft-deletes rows with a deleted_at column, what happens to foreign keys and to declared referential actions such as ON DELETE CASCADE, and how do teams handle the child rows of a soft-deleted parent?

## Foreign keys do not know about your flag A foreign key constraint says: for every non-null value in the child's referencing columns, a row with that key must exist in the parent table. "Exist" is physical. A soft delete does not change existence — it changes a column value the constraint does not look at. So a soft-deleted schema silently loses everything the referential machinery was doing for you. ## Consequence 1: referential actions never fire `ON DELETE CASCADE`, `ON DELETE SET NULL`, `ON DELETE RESTRICT` and `NO ACTION` are all triggered by a `DELETE` on the parent (or an `UPDATE` of the referenced key). Your soft delete is an `UPDATE` of an unrelated column, so none of them run. Child rows remain exactly as they were: live rows pointing at a parent the application considers gone. That matters because most reads reach these rows through joins. `SELECT … FROM order_items i JOIN orders o ON o.id = i.order_id` returns items of soft-deleted orders unless you also write `AND o.deleted_at IS NULL`. The flag has to be checked not only on the table you are querying but on every table you traverse — which is the real reason soft delete gets expensive as a schema grows. ## Consequence 2: nothing stops new children Because the parent row still exists, inserting a child that references it satisfies the constraint. The database will let you attach a new invoice to a deleted customer. A foreign key cannot be conditional — there is no `REFERENCES customers(id) WHERE deleted_at IS NULL` in SQL. If that invariant matters, your options are: - an application-level check inside the same transaction (racy unless the parent row is locked, e.g. `SELECT … FOR UPDATE` or `FOR SHARE`, since a concurrent transaction can soft-delete the parent between your check and your insert); - a `BEFORE INSERT` trigger on the child that looks up the parent's flag (same locking caveat); - a structural trick: keep a denormalized copy of the parent's live-state in the key. For example the parent has `(id, is_live)` with a unique key on both, the child stores `parent_id, parent_is_live` and references that composite key with a `CHECK (parent_is_live = true)`. This makes the database enforce it, at the cost of a compound key and an update-cascade on the flag column. Rarely worth it, but it is the only fully declarative answer. ## Consequence 3: you must implement the cascade — and its inverse "Delete this project" usually means the tasks, comments and attachments go too. With soft delete, that is a multi-statement transaction that stamps `deleted_at` down the tree. Two design questions follow. **How deep does the cascade go?** Ownership edges (task belongs to project) should cascade; reference edges (invoice references a customer) should not — you do not want soft-deleting a customer to hide their paid invoices. Decide edge by edge, exactly as you would when choosing `CASCADE` vs `RESTRICT` on a real foreign key. **How do you restore?** If you simply set every child of the project back to `deleted_at = NULL`, you also resurrect the tasks the user deleted individually last month. You need to distinguish "died with the parent" from "was already dead". The two standard techniques are: stamp all rows in the cascade with the *same* timestamp as the parent and restore only exact matches; or generate a `deletion_batch_id` (a UUID per delete operation) written to every affected row, and restore by batch. The batch id is more robust and doubles as an audit key. ## Purging: order comes back When the purge job finally hard-deletes the parent, ordinary referential rules apply again. Either delete children first (bottom-up), or lean on a declared `ON DELETE CASCADE` so a single parent delete removes the subtree. Purging in the wrong order gives you constraint violations at 3am, and purging a parent whose children were never captured by the cascade leaves rows that fail on the next delete attempt. It is worth writing the purge as an explicit, ordered, batched routine rather than trusting the constraint graph to be complete. ## Practical guidance - Put the soft-delete predicate on the *root* of each aggregate and cascade the flag to owned children, so reads can filter at one place per aggregate rather than everywhere. - Consider views (`CREATE VIEW orders AS SELECT * FROM orders_all WHERE deleted_at IS NULL`) so joins are filtered by construction. - Where the parent–child relationship must genuinely block new writes, remember the flag gives you no protection and write the check (with a lock) explicitly. - If most of the pain comes from cascade and restore semantics, that is a strong signal to switch to hard delete with an archive table, where the constraint graph does the work again.