Soft Deletes: A Modeling Guide

1. What a soft delete is and why hard delete is often the wrong choice

A soft delete marks a row as deleted without physically removing it from the table. Instead of DELETE FROM orders WHERE order_id = 42, the application sets a flag or a timestamp, and an update replaces the destructive operation. The row remains fully intact, including every relationship to other tables, only the application logic treats it from that point on as no longer existing.

The reason soft delete is preferred over a real hard delete in many systems is rarely convenience, but a genuine business requirement: a customer should be able to see their order history even if a product within it was deleted. A compliance team needs retroactively traceable data. An undo feature should be able to restore accidentally deleted rows within a time window. A hard delete destroys exactly these possibilities irrevocably the moment the database transaction commits.

At the same time, soft delete is not a trivial one line solution. As soon as a row marked as deleted keeps living in the same table as active rows, unique constraints, indexes, foreign key cascades, and practically every query throughout the application must account for this new reality. The following sections show which models have proven themselves in practice and where the typical pitfalls lie.

2. The deleted_at timestamp pattern

The most common pattern for soft delete is a nullable deleted_at column of type timestamp. As long as the value is NULL, the row counts as active. As soon as a timestamp is set, the row counts as deleted, and the timestamp itself additionally documents exactly when the deletion happened, which for audits and support requests is often just as important as the fact of deletion itself.

-- Classic deleted_at pattern for soft delete
CREATE TABLE orders (
  order_id     SERIAL PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers(customer_id),
  total_amount NUMERIC(10,2) NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at   TIMESTAMPTZ NULL           -- NULL = active, set = deleted
);

-- Soft delete instead of DELETE
UPDATE orders SET deleted_at = now() WHERE order_id = 42;

-- Query active orders
SELECT * FROM orders WHERE deleted_at IS NULL;

-- Restore: simply reset the flag
UPDATE orders SET deleted_at = NULL WHERE order_id = 42;

The advantage of this pattern lies in its simplicity and the extra information carried in the timestamp itself. Downsides only show up on closer inspection: every single query against this table must consistently add WHERE deleted_at IS NULL, otherwise deleted rows unintentionally appear in results. Many ORMs offer a global scope that handles this automatically, replacing discipline but bringing its own failure modes, for example when raw queries bypass the scope.

3. Unique constraints and soft deletes: the conflict problem

A soft delete almost inevitably collides with unique constraints as soon as a business unique value such as an email address or a product code is involved. A classic standard unique index knows no difference between active and deleted rows, it simply prevents duplicate values across the entire column. If a user account is removed via soft delete, the email address remains in the deleted row and blocks a new registration with the same address.

-- Problem: a normal unique constraint blocks re-registration
-- after soft deleting the old account
CREATE TABLE users (
  user_id    SERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,   -- applies even to deleted rows
  deleted_at TIMESTAMPTZ NULL
);

-- User is removed via soft delete
UPDATE users SET deleted_at = now() WHERE user_id = 7;

-- Re-registration with the same email fails,
-- even though the old account has long been "deleted"
INSERT INTO users (email) VALUES ('customer@example.com');
-- ERROR: duplicate key value violates unique constraint "users_email_key"

This behavior is not always unwanted, in some domains a once used email address should stay permanently blocked, for instance for fraud prevention. Everywhere reuse should be permitted from a business perspective, however, a solution is needed that applies the unique constraint only to active rows, not to the entire table including all history marked by soft delete.

4. Partial indexes for active rows

The cleanest solution to the unique constraint problem is a partial index, an index that covers only part of a table's rows, defined via a WHERE clause on the index itself. PostgreSQL and SQLite support this feature natively, MySQL and older SQL Server versions need workarounds via computed columns or filtered indexes with similar syntax.

-- PostgreSQL: partial unique index for active rows only
CREATE UNIQUE INDEX idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

-- Now reuse after a soft delete is possible
UPDATE users SET deleted_at = now() WHERE user_id = 7;
INSERT INTO users (email) VALUES ('customer@example.com');  -- works

-- SQL Server: equivalent via filtered index
-- CREATE UNIQUE INDEX idx_users_email_active
--   ON users (email)
--   WHERE deleted_at IS NULL;

-- MySQL (no native partial index): workaround via a generated column
-- ALTER TABLE users ADD COLUMN email_active VARCHAR(255)
--   GENERATED ALWAYS AS (CASE WHEN deleted_at IS NULL THEN email ELSE NULL END) STORED;
-- CREATE UNIQUE INDEX idx_users_email_active ON users (email_active);

Beyond solving the uniqueness problem, partial indexes bring another benefit: they are smaller and faster than an index over the entire table, because deleted rows, which in many systems eventually make up the majority of records, are never indexed in the first place. Queries that consistently filter for active rows benefit directly from this smaller index size, without any change to the query code itself.

5. Soft deletes and foreign keys: cascading effects

An often overlooked aspect of soft delete concerns foreign key relationships. A real DELETE with ON DELETE CASCADE automatically cleans up all dependent rows. A soft delete via UPDATE triggers no cascades at all, because from the database's point of view no deletion is happening, just an ordinary update of one column. Dependent rows in other tables remain completely untouched and thus stay invisibly marked as "active," even though their parent row has logically already disappeared.

In practice this means: if an order is removed via soft delete, its order items remain unchanged and active, unless the application explicitly ensures that dependent tables also receive their own soft delete. A clean approach is a trigger that, when deleted_at is set on the parent table, propagates the same timestamp to all dependent tables, instead of relying on scattered application code that is easy to forget.

6. Alternative: status column instead of timestamp

Instead of a single deleted_at timestamp, some systems model a row's state via a generic status column, for example as an enum with values like active, archived, and deleted. This pattern pays off as soon as a row passes through more than two meaningful states, for example when a distinction is needed between a row the user archived and one that was actually deleted.

-- Status column instead of a plain soft delete flag
CREATE TYPE record_status AS ENUM ('active', 'archived', 'deleted');

CREATE TABLE documents (
  document_id  SERIAL PRIMARY KEY,
  title        VARCHAR(255) NOT NULL,
  status       record_status NOT NULL DEFAULT 'active',
  status_changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Archive instead of delete
UPDATE documents SET status = 'archived', status_changed_at = now()
WHERE document_id = 15;

-- Show only active documents
SELECT * FROM documents WHERE status = 'active';

-- A partial index still makes sense for unique cases
CREATE UNIQUE INDEX idx_documents_title_active
  ON documents (title) WHERE status = 'active';

The advantage of a status column over a plain timestamp flag is expressiveness: more than two states can be represented without combining several boolean or timestamp columns. The downside is added complexity in application logic, because every status change should be treated as an explicit state transition, ideally with a rule defining which transitions are allowed and which are not.

7. Alternative: archive table instead of a flag

The third alternative dispenses entirely with a flag in the main table and instead physically moves deleted rows into a separate archive table with an identical structure. A soft delete then technically becomes an INSERT into the archive table followed by a real DELETE in the main table, usually within a single transaction or automated via a trigger.

-- Archive table with identical structure plus metadata
CREATE TABLE orders_archive (
  LIKE orders INCLUDING ALL,
  archived_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  archived_by  INTEGER REFERENCES users(user_id)
);

-- "Soft delete" as a move within one transaction
BEGIN;
INSERT INTO orders_archive
SELECT o.*, now(), 42 FROM orders o WHERE o.order_id = 7781;

DELETE FROM orders WHERE order_id = 7781;
COMMIT;

-- Main table stays lean, no deleted_at filtering needed
SELECT * FROM orders;  -- guaranteed to contain only active orders

The big advantage of this model: the main table is guaranteed to stay lean and free of deleted rows, so no query can ever forget a filter predicate, because deleted data simply no longer lives there. The downside is increased complexity for restorations, which now require an actual move back, plus double maintenance effort for schema changes, because main table and archive table must be kept in sync.

8. Application level pitfalls: the forgotten WHERE clause

The most common mistake with soft delete using the deleted_at or status pattern is simply a forgotten filter condition. A new report, a manual analytics query, or a new API route that writes directly against the table instead of using the ORM scope suddenly shows deleted rows in analyses, exports, or even public list views. Such mistakes are especially insidious because they cause no crash, just slightly wrong numbers.

A second common mistake concerns aggregations: a COUNT(*) query without a filter condition automatically counts deleted rows too, which systematically distorts dashboards and KPI reports. Database views that hardwire the filter WHERE deleted_at IS NULL and serve as the sole interface for reporting tools significantly reduce this risk, because the filter logic is centralized in one place instead of being repeated in every single query.

9. Soft delete models compared

The following table compares the three discussed models for soft delete by implementation effort, query complexity, and suitability for restorations.

10. Summary

Soft delete solves a real business need, namely preserving data for history, compliance, or undo features, without physically removing it. The most popular pattern, a nullable deleted_at column, is simple to implement but brings unique constraint conflicts and the risk of forgotten filter conditions, both of which can be mitigated with partial indexes and centralized database views.

A status column pays off as soon as more than two states are needed, an archive table pays off as soon as the main table must never contain deleted rows under any circumstances. All three models share the same basic rule: soft delete without careful indexing and without centralized filter logic almost inevitably leads to data inconsistencies that only surface on close inspection.

11. FAQ: Soft Deletes Modeling Guide