Composite constraint where deleted_at is NULL

Question

I need to make a composite constraint in Laravel, which only consider non soft-deleted rows. So if the entity was deleted, the deleted_at is not null and data_1, data_2, data_3, data_4, data_5 can be inserted again.

Best I could come up with is this (table and column names were defined in previous migrations):

public function up()
{
    Schema::table('table_name', function (Blueprint $table) {
        $table->unique(['data_1', 'data_2', 'data_3', 'data_4', 'data_5'])
            ->whereNull('deleted_at');
    });
}

But it doesn't work. The constraint applies even if deleted_at is not null.

I've been looking through other post with similar issues but their solutions have different approaches non related with a migration.

Answer 1

Turns out that making a constraint wasn't the correct approach for the problem.

A unique index allows to make a composition of columns unique, and also admit conditions.

In raw sql statement it is:

public function up()
{
    DB::statement('CREATE UNIQUE INDEX table_unique_preferredNameOfIndex ON schema.table 
        USING btree (data_1, data_2, data_3, data_4, data_5) WHERE (deleted_at IS NULL)');
}

Not strictly the same but it gets the job done.

If a rollback is needed, just drop the index:

public function down()
{
    DB::statement('DROP INDEX schema.table_unique_preferredNameOfIndex');
}