Why use it
- Recovering accidentally deleted data
- Audit and compliance: data is retained as required by law
- Preserving references: if an Order references a deleted User, the record stays
Implementation
-- Migration: add the field
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;
-- "Delete"
UPDATE users SET deleted_at = NOW() WHERE id = 42;
-- Queries ignore "deleted" records
SELECT * FROM users WHERE deleted_at IS NULL;
Downsides
- The table grows and is never cleaned — archiving is needed
- Unique constraints: a deleted email may block a new account
- More complex JOINs and queries