Unique index on soft deleted table MySQL using Laravel

May 11, 20232 min read

When we are using SoftDeletes trait in Laravel Model, sometimes we need to ensure the data is unique. We can add UNIQUE INDEX to the DB to guarantee that the data is unique. However, when we are dealing with soft deleted table, the index doesn't work as we intend them to because UNIQUE INDEX does not work on null values.

We can add a condition to the UNIQUE INDEX to make sure that the INDEX is working when deleted_at equals to NULL ().

Add this to your migration file to create unique index on soft deleted table.

public function up()
  {
      DB::unprepared('CREATE UNIQUE INDEX unique_username ON users (
          username,
          (IF(deleted_at, NULL, 1))
      );');
  }

(IF(deleted_at, NULL, 1)) means that IF deleted_at IS NULL, return 1. Otherwise, it is NULL. For example, we have users table with 0 rows and the UNIQUE index already created.

INSERT INTO users (username, deleted_at) VALUES (handi, NULL) -- pass
INSERT INTO users (username, deleted_at) VALUES (handi, NULL) -- error
INSERT INTO users (username, deleted_at) VALUES (handi, '2023-05-12 01:12:00') -- pass

Explanation:

  • First INSERT command will work because no duplicate values found
  • Second INSERT fail because the VALUES (handi,null)evaluates to(handi, 1)` causing duplicate data found
  • Third INSERT works because VALUES (handi, '2023-05-12 01:12:00') does not break the UNIQUE INDEX due to the non NULL value of deleted_at

Thanks to this article that shows how to create the index in MySQL and Postgresql.