Soft delete — marking rows as deleted rather than removing them

Beginner5 min read·eng-15-020

sqlinterview

Concept

Soft delete — marking a row as "deleted" by setting a timestamp (or flag) rather than physically removing it from the database. The row remains in the table but is excluded from normal queries.

Hard delete (physical delete): DELETE FROM users WHERE id = 1 — row is gone forever. Soft delete: UPDATE users SET deleted_at = NOW() WHERE id = 1 — row stays but is hidden.

Why soft delete:

Auditability: Business or legal requirement to keep records of everything, even after "deletion."
Undo/restore: Restore a deleted record. users/{id}/restore.
Referential integrity: Keeping FK references intact even after "deletion."
Recycle bin: Users can "delete" and "recover" items (like email trash).
Analytics: Historical data for reporting on deleted records.

The deleted_at column: Standard convention (used by Laravel's SoftDeletes trait). NULL = not deleted. Timestamp = deleted at that time.

Laravel's SoftDeletes trait:

Adds deleted_at column automatically (via $table->softDeletes() in migration).
delete() sets deleted_at = NOW() instead of deleting.
All queries automatically get WHERE deleted_at IS NULL via a global scope.
withTrashed() includes soft-deleted records.
onlyTrashed() returns ONLY soft-deleted records.
restore() sets deleted_at = NULL.
forceDelete() physically deletes the row.

Downsides:

Storage bloat: Deleted rows accumulate forever.
Query complexity: Must always exclude soft-deleted rows in raw queries.
UNIQUE constraints: A soft-deleted email still blocks the UNIQUE index. Work around with WHERE deleted_at IS NULL partial unique index (PostgreSQL) or include deleted_at in the unique key.

Code Example

php

<?php
// Migration — add deleted_at column
Schema::table('users', function (Blueprint $table) {
    $table->softDeletes(); // adds: deleted_at TIMESTAMP NULL DEFAULT NULL
});

// Model
class User extends \Illuminate\Database\Eloquent\Model
{
    use \Illuminate\Database\Eloquent\SoftDeletes; // that's all you need!
}

// Using soft deletes
$user = User::find(1);
$user->delete(); // UPDATE users SET deleted_at = NOW() WHERE id = 1

// Normal queries — soft-deleted users are INVISIBLE
$users = User::all(); // WHERE deleted_at IS NULL — Alice not included if deleted

// Include soft-deleted
$users = User::withTrashed()->get(); // all users including deleted

// Only deleted
$deleted = User::onlyTrashed()->get();

// Restore
$user = User::onlyTrashed()->find(1);
$user->restore(); // SET deleted_at = NULL

// Really delete
$user->forceDelete(); // DELETE FROM users WHERE id = 1

// UNIQUE constraint problem — soft delete + unique email
// Problem: user@example.com soft-deleted, new user tries to register with same email
// UNIQUE constraint blocks the insert even though the row is "deleted"

// Solution 1: Include deleted_at in unique constraint (MySQL workaround)
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('email');
    $table->softDeletes();
    // deleted_at is DATETIME NULL — NULLs don't count as duplicates in MySQL
    // So email + deleted_at composite unique works!
    $table->unique(['email', 'deleted_at']); // multiple NULLs allowed → soft-deleted + active can coexist
});

// Solution 2: Partial unique index (PostgreSQL)
\DB::statement("
    CREATE UNIQUE INDEX users_email_unique
    ON users (email)
    WHERE deleted_at IS NULL
"); // only enforces uniqueness for non-deleted rows

// Solution 3: On restore/register — check for soft-deleted record first
$existing = User::onlyTrashed()->where('email', $email)->first();
if ($existing) {
    $existing->restore();
    $existing->update($newData);
} else {
    User::create($newData);
}

PreviousUpsert — INSERT or UPDATE in a single atomic operationVocabulary — Database & SQL NextCoupling — how much one module depends on the internals of anotherVocabulary — Architecture & Design