Soft Delete Pattern in Database Design

A soft delete marks a row as deleted without physically removing it from the table, typically through an is_deleted boolean or a deleted_at timestamp column, so the data still exists in the database but is excluded from normal application queries. This is a deliberate alternative to a hard delete (an actual DELETE statement that permanently removes the row), and choosing between the two is a real design decision with meaningful trade-offs, not just a style preference.

Soft delete is common in production systems because deleted data is very often needed again — for recovery from a mistaken deletion, for audit and compliance requirements, for historical reporting, or because other rows still reference the "deleted" row through foreign keys and cannot simply lose that reference.

Why it matters

Users delete things by mistake, and businesses often must keep records that have been "removed". Marking a row as deleted instead of erasing it allows recovery and preserves history. The pattern has costs, though: every query has to remember to exclude deleted rows, and unique constraints need extra thought.

The deleted_at Pattern

A nullable deleted_at TIMESTAMP column is generally preferable to a plain is_deleted boolean, because it captures both the fact of deletion and exactly when it happened, at no extra storage cost. Every query that should only see "live" rows adds a WHERE deleted_at IS NULL clause (often wrapped in a view or an ORM default scope so it isn't forgotten), and "deleting" a row becomes an UPDATE that sets deleted_at to the current timestamp instead of a DELETE.

Trade-offs vs. Hard Delete

Soft delete preserves recoverability, audit history, and referential integrity for rows still referenced elsewhere, but it comes at a real cost: every query in the application must consistently remember to filter out soft-deleted rows, unique constraints become more complicated (a deleted user's email should probably be reusable by a new signup, but a plain UNIQUE constraint on email would block that), and the table grows indefinitely since nothing is ever actually removed. Hard delete is simpler and keeps tables smaller, but it is permanent, breaks any foreign keys still pointing at the row (unless cascaded), and destroys any audit trail of what used to exist.

• Choose soft delete when data must be recoverable, when compliance or audit rules require history, or when other tables reference the row.
• Choose hard delete for genuinely transient or sensitive data (e.g. expired session tokens, data under a legal "right to erasure" requirement) where retention itself is the risk.
• Hybrid approach: soft delete immediately, then a scheduled job permanently purges rows past a defined retention period.

Handling Uniqueness and Foreign Keys with Soft Delete

A plain UNIQUE constraint on a column like email breaks the moment a soft-deleted row exists with that email, since the constraint doesn't know the row is "gone." Many databases support a partial unique index (UNIQUE ... WHERE deleted_at IS NULL) that enforces uniqueness only among live rows, which is the correct fix. Foreign keys pointing at soft-deleted rows also need a deliberate policy: the application typically must still be able to display an order placed by a since-deleted customer, so the customer record is kept (soft-deleted) rather than hard-deleted out from under existing orders.

Example

CREATE TABLE customers (
    id          BIGINT PRIMARY KEY,
    full_name   VARCHAR(150) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    deleted_at  TIMESTAMP NULL
);

-- Enforce uniqueness only among live (non-deleted) rows
CREATE UNIQUE INDEX uq_customers_email_live
    ON customers (email)
    WHERE deleted_at IS NULL;

-- "Deleting" a customer is an UPDATE, not a DELETE
UPDATE customers SET deleted_at = CURRENT_TIMESTAMP WHERE id = 42;

-- Every normal application query filters out soft-deleted rows
SELECT id, full_name, email FROM customers WHERE deleted_at IS NULL;

Customer id 42 remains physically in the table (its orders and other
references stay valid), but disappears from normal application queries.
Because the unique index only covers rows where deleted_at IS NULL, a
new customer can sign up again later with the same email address that
the deleted account used, without a constraint violation.

Exercise

Add soft delete to the users table: a deleted_at column, and a unique index on email that applies only to rows that are not deleted, so that the address can be reused. Soft-delete Asha, then register a new user with the same email. Count the active users, and then all rows.

Expected output: active, 1, total, 2 (one per line)

-- TODO: users (id, email, deleted_at)

-- TODO: a unique index on email that covers only rows where deleted_at IS NULL

INSERT INTO users (id, email) VALUES (1, 'asha@example.com');

-- TODO: soft-delete user 1 by setting deleted_at to the current time

INSERT INTO users (id, email) VALUES (2, 'asha@example.com');

SELECT COUNT(*) AS active FROM users WHERE deleted_at IS NULL;
SELECT COUNT(*) AS total FROM users;

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  deleted_at TIMESTAMP
);

CREATE UNIQUE INDEX users_email_active ON users (email) WHERE deleted_at IS NULL;

INSERT INTO users (id, email) VALUES (1, 'asha@example.com');

UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1;

INSERT INTO users (id, email) VALUES (2, 'asha@example.com');

SELECT COUNT(*) AS active FROM users WHERE deleted_at IS NULL;
SELECT COUNT(*) AS total FROM users;

Quick quiz

Interview questions