Terkadang mau bikin column email yang unique, tapi bakal jadi masalah kalau kita menggunakan softdeletes fitur.

Solusi

Solusinya dengan menggunakan partial index berikut ini:

ALTER TABLE users ADD COLUMN email_unique varchar(512) 
    GENERATED ALWAYS AS (
        CONCAT(email, '#', IF(deleted_at IS NULL, '-', deleted_at))
    ) VIRTUAL;

CREATE UNIQUE INDEX email_unique ON users (email_unique);

Referensi

• Laravel Unique Validation with Soft Delete Example
• Original Twitter Thread by Tobias Petry