SQL Soft Deletes: Implementation Patterns, Indexes, and Common Pitfalls
By Sharon Ben-Moshe · October 13, 2026 · 1 min read
Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Explore SQL soft delete architectures. Compare deleted_at timestamps vs boolean flags, unique constraint collisions, partial indexes, and RLS policies.
Quick answer
Soft deletion marks database records as inactive (using a deleted_at TIMESTAMP NULL column) instead of physically executing SQL DELETE. While it preserves audit history, soft deletes introduce critical pitfalls: unique constraint collisions on deleted rows, foreign key cascade dilemmas, and query performance degradation from filtering WHERE deleted_at IS NULL on every query. Solve unique collisions using partial indexes.
FAQs
Why is 'deleted_at TIMESTAMP' better than 'is_deleted BOOLEAN'?
`deleted_at` conveys two pieces of information: whether the row is deleted (`deleted_at IS NOT NULL`) and exactly when it was deleted, which is essential for data retention policies and audit logs.
How do you enforce UNIQUE constraints with soft deletes?
In traditional unique constraints, a user cannot re-register an email that was previously soft-deleted. Fix this using a partial unique index: `CREATE UNIQUE INDEX uq_users_active_email ON users (email) WHERE deleted_at IS NULL;`.
How do you ensure developers don't forget 'WHERE deleted_at IS NULL'?
Use PostgreSQL Row-Level Security (RLS) policies or database views that automatically exclude soft-deleted rows by default.
What should happen to foreign key child records when a parent is soft-deleted?
Physical `ON DELETE CASCADE` does not trigger on soft deletes. You must either implement an application transaction that cascades `deleted_at` down to child records or use database triggers.
Keep reading
SQL Query Optimization and Tuning: The End-to-End Practitioner's Guide
By Sharon Ben-Moshe · Oct 13, 2026
The definitive guide to SQL query optimization. Master EXPLAIN execution plans, SARGable predicates, covering indexes, and keyset cursor pagination.
Read postHow SQL Query Engines Actually Execute Queries: The Complete Lifecycle
By Sharon Ben-Moshe · Oct 13, 2026
Journey through the SQL query lifecycle. Explore lexical parsing, Abstract Syntax Trees, logical optimization, Cost-Based Optimizers, and Volcano execution.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.