Records that are hidden rather than removed.
WHAT IT MEANS
Marking a row as deleted rather than removing it.
WHY IT IS USED
Recovery from mistakes Audit and history References from other records remaining valid
WHAT IT COSTS
Every query must exclude deleted rows Unique constraints stop working as expected Tables grow indefinitely Forgetting the condition exposes deleted data
WHY THE UNIQUE PROBLEM IS SUBTLE
A deleted record still occupies the unique value, so the same value cannot be reused.
WHAT TO DO ABOUT IT
Include the deletion marker in the unique constraint, where the database permits Or genuinely remove the row
WHAT TO INDEX
The deletion marker alongside commonly filtered columns.
WHY
Otherwise every query filters after reading.
WHAT TO ENFORCE
That the exclusion is applied centrally, not repeated in every query.
WHY
One omission exposes deleted records.
WHAT TO DECIDE
How long deleted rows are kept.
WHAT TO DO AFTER THAT
Actually remove them.
WHY
Indefinite retention creates obligations under data protection rules.
WHAT TO CONSIDER INSTEAD
Moving deleted rows to an archive table.
WHAT THAT PROVIDES
A clean main table, with history preserved.
WHAT TO CHECK
That deletion requests from people are genuinely honoured.
