The Soft Delete Trap: Mastering Unique Constraints in Laravel

The Ghost Record Collision

When building enterprise B2B SaaS platforms at Smart Tech Devs, hard-deleting database records is an operational liability. If a client accidentally deletes a workspace, you need the ability to restore it instantly. The industry standard solution is Laravel's SoftDeletes trait, which simply stamps a deleted_at timestamp on the row instead of physically removing it from the database.

However, introducing Soft Deletes creates a massive, hidden architectural collision with Unique Database Constraints. Imagine you have a users table where the email column must be unique. A user signs up with ceo@acme.com. A week later, they delete their account (soft delete). A month later, they try to sign up again with ceo@acme.com.

Your Laravel validation might pass (if configured to ignore soft-deleted rows), but the moment Eloquent tries to insert the record, the PostgreSQL/MySQL database throws a fatal Integrity constraint violation: 1062 Duplicate entry. The database doesn't care about your PHP deleted_at column; it only sees two identical emails. To build robust architectures, your physical database schema must understand your soft-delete logic.

The Solution: Compound Unique Indexes

To fix this, we cannot rely on a simple unique index on the email column. We must create a Compound Unique Index that combines the email column with the deleted_at column. But there is a catch: in standard SQL, multiple NULL values are not considered equal. If deleted_at is NULL, the unique constraint behaves unexpectedly across different SQL dialects.

Architecting the Schema in PostgreSQL and MySQL

The most bulletproof way to handle this in modern database engines is using a Partial Unique Index (native to PostgreSQL) or generated virtual columns (MySQL).

Here is the clean, enterprise-grade migration pattern for PostgreSQL using Laravel's Blueprint:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('email');
            $table->string('name');
            $table->timestamps();
            $table->softDeletes(); // Adds the 'deleted_at' column

            // ❌ THE ANTI-PATTERN: This will crash if a soft-deleted user signs up again
            // $table->unique('email'); 
        });

        // ✅ THE ENTERPRISE PATTERN (PostgreSQL):
        // We create a partial unique index directly via raw SQL.
        // This enforces uniqueness on the email ONLY for active, non-deleted rows!
        DB::statement('
            CREATE UNIQUE INDEX users_email_active_unique 
            ON users (email) 
            WHERE deleted_at IS NULL;
        ');
    }

    public function down(): void
    {
        Schema::dropIfExists('users');
    }
}

Updating FormRequest Validation

Your database is now secure, but you must also instruct Laravel's HTTP validation layer to respect the soft-delete boundary so users receive clean JSON error messages instead of 500 Server Errors.

namespace App\Http\Requests;

use Illuminate\Foundation\Http\FormRequest;
use Illuminate\Validation\Rule;

class StoreUserRequest extends FormRequest
{
    public function rules(): array
    {
        return [
            'email' => [
                'required',
                'email',
                // Tell Laravel to only check uniqueness against rows where deleted_at is NULL
                Rule::unique('users')->whereNull('deleted_at'),
            ],
            'name' => ['required', 'string'],
        ];
    }
}

The Engineering ROI

By shifting your unique constraint logic into partial database indexes, you achieve absolute data integrity. You eliminate fatal 500 SQL errors, allow users to seamlessly re-register after deleting their accounts, and preserve your historical audit trails perfectly without compromising database-level uniqueness guarantees.