Soft deletes in Laravel 11, Laravel 12 and Laravel 13 all Version Support impact database indexes in two major ways: they can silently degrade query performance if the deleted_at column isn not indexed, and they can break unique constraints because "deleted" rows still physically exist in the table.
Performance: The Missing Index Problem
By default, Laravel is softDeletes() method creates a nullable deleted_at timestamp column but does not add an index to it. Since Laravel automatically appends WHERE deleted_at IS NULL to almost every query, this can force the database to scan the entire table to find active records.
Example (The Problem):
// Migration: No index on deleted_at
Schema::create( posts , function (Blueprint $table) {
$table->id();
$table->string( notitle );
$table->softDeletes(); // Creates deleted_at but NO index
});
If you have 1 million posts and only 100 are deleted, a query like Post::all() (which translates to SELECT * FROM posts WHERE deleted_at IS NULL) might perform a full table scan, ignoring the deleted_at column entirely because the vast majority of rows have NULL in that field.
Example (The Fix): You should explicitly add an index to the deleted_at column, ideally as part of a composite index that matches your query patterns.
Schema::table( posts , function (Blueprint $table) {
// If you often query by user_id and exclude deleted
$table->index([ user_id , deleted_at ]);
});
Some developers also prefer using a boolean is_deleted flag with an index instead of a timestamp, as boolean indexes are often smaller and more predictable for the query planner .
Unique Constraints: The "Ghost" Data Problem
This is a more critical logical issue. A soft-deleted row still exists in the database. Therefore, if you have a standard unique index (e.g., on email), you cannot insert a new record with the same email until the old one is force-deleted. The database sees the soft-deleted row and blocks the insertion .
Example (The Problem):
// User john@example.com is soft-deleted
$user = User::where( email , john@example.com )->first();
$user->delete(); // deleted_at is set, but row remains// This will throw a "Duplicate entry" error
User::create([ email => john@example.com , name => John Doe ]);
Example (The Fix: Generated Column): To allow re-registration while maintaining the soft delete, you can use a generated column that is 1 for active rows and NULL for deleted rows. Then, create a unique index on [email, active]. Because SQL allows multiple NULL values in a unique index, you can have unlimited deleted users with the same email, but only one active user.
Schema::create( users , function (Blueprint $table) {
$table->string( email );
$table->softDeletes();
// Generated column: 1 if active, NULL if soft-deleted
$table->boolean( active )->storedAs( IF(deleted_at IS NULL, 1, NULL) );
// Composite unique index
$table->unique([ email , active ]);
});
Alternative Fix (Composite Index with deleted_at): A simpler, though slightly less robust, approach is to include deleted_at in the unique index. Since NULL values are treated as distinct in unique indexes by most databases (like MySQL), this also allows multiple soft-deleted rows with the same email.
$table->unique([ email , deleted_at ]);
Note: This relies on database-specific behavior regarding NULL handling in unique indexes.
Summary
In short, soft deletes require you to be proactive with your schema: always add an index that includes deleted_at to keep queries fast, and adjust your unique indexes (using a generated column or composite key) if you need to allow re-use of values like emails after a soft delete.