Soft Deletes vs. Unique Constraints: The Duplicate SKU Bug That Hid for a Year

A product manager once asked me a simple question: "Why does the warehouse screen show two different products with the same SKU?"

I was sure it was impossible. The products table had a unique index on sku. The form validated uniqueness. We had tests. And yet, there they were: two active rows, same SKU, different prices, each one collecting its own stock movements for months.

The bug was not in a controller or a query. It was in a single line of a migration that looked perfectly reasonable in code review. This post is the story of that line, why it fails, and the patterns I now use whenever soft deletes and uniqueness meet in a Laravel business app.

The problem: soft deletes and unique keys disagree

Soft deletes are almost mandatory in ERP and CRM systems. You rarely want to physically delete a product, a customer or a supplier, because invoices, orders and audit logs still point at it. Laravel makes this easy with the SoftDeletes trait and a deleted_at column.

Business keys are just as common: a product SKU, a customer tax number, a supplier code, an employee badge number. These must be unique, and the obvious place to enforce that is the database.

Here is where the two collide. Version one of our migration looked like this:

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('sku', 64)->unique();
    $table->string('name');
    $table->softDeletes();
    $table->timestamps();
});

It worked until a user deleted a product created by mistake and then tried to create it again with the correct data. The deleted row still held the SKU, so the insert failed. The validation rule had the same opinion, because Rule::unique('products', 'sku') checks the table, not the Eloquent model, so soft-deleted rows count too.

A ticket came in: "I can't reuse a SKU after deleting a product." The fix that shipped was this:

$table->dropUnique(['sku']);
$table->unique(['sku', 'deleted_at']);

The reasoning sounded solid: a deleted row has a timestamp in deleted_at, an active row has NULL, so the pair (sku, deleted_at) should be unique for active products and free for deleted ones.

That reasoning is wrong.

Why the composite index does nothing for active rows

In SQL, NULL is not equal to NULL. MySQL's documentation says it plainly: a UNIQUE index permits multiple NULL values for columns that can contain NULL. PostgreSQL behaves the same way by default.

So for every active product, the index sees (sku, NULL), and two rows with ('ABC-100', NULL) are not considered duplicates. The composite index only prevents two deleted rows with the same SKU and the exact same deletion timestamp, which is the one case nobody cares about.

Why did it take a year to notice? Because the form validation still caught most duplicates. The real damage came from paths that skipped the form:

• a nightly CSV import that used upsert() keyed on id, not sku
• an API endpoint added later by another team that validated only the format
• two users saving the same new product at the same moment (validation passed for both, because neither row existed yet)

Validation is a user experience feature. The database constraint is the guarantee. We had silently removed the guarantee.

The fix: make the database understand "active"

The goal is simple to state: the SKU must be unique among rows where deleted_at IS NULL. There are two good ways to express that, depending on your database.

PostgreSQL: a partial unique index

PostgreSQL supports partial indexes, which is exactly this rule. I use a raw statement in the migration so the intent is explicit:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        DB::statement(
            'CREATE UNIQUE INDEX products_sku_active_unique
             ON products (sku)
             WHERE deleted_at IS NULL'
        );
    }

    public function down(): void
    {
        DB::statement('DROP INDEX IF EXISTS products_sku_active_unique');
    }
};

Active rows must have unique SKUs. Deleted rows are not in the index at all, so you can delete and recreate a SKU as often as you like.

MySQL / MariaDB: a generated column

MySQL has no partial indexes, but it has generated columns, and Laravel's schema builder supports them with virtualAs() and storedAs(). The trick is to create a column that holds the SKU for active rows and NULL for deleted ones, then put a normal unique index on it. Because multiple NULLs are allowed, deleted rows never conflict.

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropUnique(['sku', 'deleted_at']);

            $table->string('active_sku', 64)
                ->nullable()
                ->storedAs('CASE WHEN deleted_at IS NULL THEN sku END');

            $table->unique('active_sku');
        });
    }

    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropUnique(['active_sku']);
            $table->dropColumn('active_sku');
            $table->unique(['sku', 'deleted_at']);
        });
    }
};

A few notes from running this in production:

• I use CASE WHEN ... END instead of MySQL's IF() so the same expression reads naturally on other engines.
• storedAs() costs a little disk space but is the least surprising option. InnoDB can also index virtual columns; pick one and stay consistent.
• Never write to active_sku from the application. Keep it out of $fillable and treat it as read-only.

Clean the data before you add the constraint

If the bug has already happened, the new unique index will fail to build. Find the duplicates first:

SELECT sku, COUNT(*) AS active_rows, GROUP_CONCAT(id) AS product_ids
FROM products
WHERE deleted_at IS NULL
GROUP BY sku
HAVING COUNT(*) > 1;

(GROUP_CONCAT is MySQL; on PostgreSQL use string_agg(id::text, ',').)

Merging duplicates is a business decision, not a technical one. Someone has to decide which row survives and where its stock movements, open orders and price history go. Put that in a reviewed data migration, not in a quick fix on production.

Keep validation, but make it agree with the database

Validation still matters, because a friendly message beats a 500 error. Laravel's Unique rule has a withoutTrashed() method that ignores soft-deleted rows, which now matches the database rule exactly:

use Illuminate\Validation\Rule;

public function rules(): array
{
    $product = $this->route('product');

    return [
        'sku' => [
            'required',
            'string',
            'max:64',
            Rule::unique('products', 'sku')
                ->withoutTrashed()
                ->ignore($product?->id),
        ],
        'name' => ['required', 'string', 'max:255'],
    ];
}

Validation and constraint now describe the same rule. Validation gives the nice message; the constraint stops races, imports and other teams' endpoints.

Restoring is now a conflict

Here is the part people forget. Once SKUs can be reused, a deleted product may share its SKU with a newer active product. Calling restore() on the old one will violate the constraint. Since Laravel 10, unique violations throw Illuminate\Database\UniqueConstraintViolationException, so you can turn the error into a clear message:

use Illuminate\Database\UniqueConstraintViolationException;
use Illuminate\Validation\ValidationException;

public function restore(Product $product): RedirectResponse
{
    try {
        $product->restore();
    } catch (UniqueConstraintViolationException $e) {
        throw ValidationException::withMessages([
            'sku' => "Another active product already uses SKU {$product->sku}. "
                . 'Change one of the SKUs before restoring.',
        ]);
    }

    return back()->with('status', 'Product restored.');
}

Route model binding needs ->withTrashed() on the route for this to find the deleted product.

Common pitfalls

Case and whitespace. On a case-insensitive collation, abc-100 and ABC-100 collide; on PostgreSQL they don't. Decide what "the same SKU" means and normalize on save (for example trim and uppercase in a mutator) so the database and the business agree.

Testing on a different engine. If your tests run on SQLite and production runs on MySQL, your constraint tests prove very little. Run at least the schema and integrity tests against the real engine.

Bulk writes skip Eloquent. DB::table()->insert(), upsert() and raw imports never touch your form requests. That is exactly why the rule must live in the database.

Other business keys. SKU is the example, but the same mistake hides in customer tax numbers, supplier codes, user emails and document numbers. Grep your migrations for unique([ together with deleted_at.

Foreign keys on the business key. If other tables reference sku instead of id, reusing a SKU silently re-points history to a new product. Reference the primary key.

Summary

• A unique index on (column, deleted_at) does not protect active rows, because NULL values never collide.
• On PostgreSQL, use a partial unique index WHERE deleted_at IS NULL.
• On MySQL, index a generated column that is NULL for deleted rows.
• Use Rule::unique(...)->withoutTrashed() so validation matches the constraint.
• Handle UniqueConstraintViolationException on restore, and clean existing duplicates before adding the index.

The lesson I took away is bigger than soft deletes: when a fix makes a ticket go away, ask what guarantee it removed. Our guarantee lived in one line, and nobody noticed when it changed.

Which business key in your app would you bet is truly unique today, and have you checked it against the active rows?