May 11, 2023 — 2 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 theVALUES
(handi,null)evaluates to
(handi, 1)` causing duplicate data found - Third
INSERT
works becauseVALUES (handi, '2023-05-12 01:12:00')
does not break theUNIQUE INDEX
due to the nonNULL
value ofdeleted_at
Thanks to this article that shows how to create the index in MySQL and Postgresql.