Soft Delete Pattern

Also Known As

TL;DR

Explanation

Soft delete adds a nullable DATETIME column (commonly deleted_at or is_deleted). All queries filter WHERE deleted_at IS NULL to exclude deleted records. Advantages: data recovery, audit trail, referential integrity preservation. Disadvantages: all queries need the filter, indexes are less efficient as soft-deleted rows accumulate, unique constraints must account for soft-deleted duplicates. Hard deleting after a retention period (archiving) is common.

Common Misconception

Why It Matters

Common Mistakes

• Forgetting the WHERE deleted_at IS NULL filter in some queries — returning deleted records.
• Unique constraints broken by soft-deleted duplicates — a deleted email re-registered violates UNIQUE.
• Not archiving old soft-deleted rows — table size grows indefinitely.

Avoid When

• Avoid soft delete for high-volume append-only data (logs, events) — deleted rows accumulate and degrade performance.
• Do not use soft delete without a cleanup/archive strategy — tables grow indefinitely.

When To Use

• Use soft delete for user data, financial records, and any content requiring audit trails or recovery.
• Use soft delete when related records reference the row via foreign keys — hard delete would violate referential integrity.

Code Examples

-- Hard delete — irreversible, breaks foreign keys, loses history
DELETE FROM users WHERE id = ?;

-- Schema
ALTER TABLE users ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL;
CREATE INDEX idx_users_deleted ON users (deleted_at);

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = ?;

-- All queries filter deleted
SELECT * FROM users WHERE deleted_at IS NULL;

// PHP: unique email workaround for soft-deleted
$stmt = $pdo->prepare(
    'SELECT id FROM users WHERE email = ? AND deleted_at IS NULL'
);

References

• ↗ https://laravel.com/docs/11.x/eloquent#soft-deleting

Tags

Related Terms