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.

What are you trying to do ? You cannot make some columns/rows unique based on a condition... the whole set (`data_1`, `data_2`, `data_3`, `data_4`, `data_5`) is going to unique **ALWAYS**... What are you trying to achieve ? This `unique` constraint based on a condition has to be done on your business logic. You can still have some DB uniqueness indexes, but explain more please. — matiaslauriti, Jun 18 '21 at 16:48
Does this answer your question? [Can I conditionally enforce a uniqueness constraint?](https://stackoverflow.com/questions/18293543/can-i-conditionally-enforce-a-uniqueness-constraint) — matiaslauriti, Jun 18 '21 at 16:49
It answers what i feared but it doesn't solve the problem. Or at least not the way i though. I tried (data_1, data_2, data_3, data_4, data_5, deleted_at) to be unique at first. But turns out that NULL is always unique in my DB. So that would allow duplicates. I could set a default string 'NULL' to avoid that but maybe there was a more elegant solution — Agustin Nigrelli, Jun 18 '21 at 17:03
The only possible solution is to do this check in Laravel (as business logic), because, as you said, you rely on if that row is soft deleted or not, so it could repeat it self, but for business logic, having it deleted is okay, but is not okay is to have 2 equals with `deleted_at = null`, that would be bad. So you have to do business logic to handle this. — matiaslauriti, Jun 18 '21 at 17:13

score 1 · Accepted Answer · answered Jun 18 '21 at 18:59

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');
}

Composite constraint where deleted_at is NULL

1 Answers1