【MySQL】論理削除されていない範囲で値がユニークであることを約束する制約の作り方

　論理削除はデータベース上にレコードを残しておくが、アプリケーション上では削除扱いとする手法です。これはデスクトップのゴミ箱のようにいざという時にデータを回復しやすくなる仕組みです。もし物理削除（実際にレコードを削除する方法）ですと復元のためにはバックアップを使う必要があり、手間がかかります。論理削除は多くのフレームワークで簡単にできるようになっています。

　論理削除の難しい点として、削除されていない範囲でユニークな値を取り扱う方法です。よくある論理削除のテーブル定義は次のようなもので削除済みを表すカラムを用います。

create table users
(
    user_id    bigint unsigned auto_increment primary key,
    login_id   varchar(255) not null comment 'ログインID。このIDは論理削除されていない範囲でユニークである必要があります',
    deleted_at datetime     null comment '削除日時'
) comment 'ユーザー';

　上記例では deleted_at がnullならば未削除、nullでなければ削除済みとなります。このような状態で単に login_id にユニーク制約をつけると、削除済みの login_id が使えずユーザーからすると退会したはずなのに退会した扱いになってないように見えたりしてしまいます。アプリケーション上のバリデーション等で実質的なユニークにするのがよくある方法ですが、データを登録する経路や方式が多くなってくるとコーディングやテストが面倒になってゆき、ミスが生まれやすくなります。そこで重複しそうになってもエラーで済ませるためにデータベース上の制約で削除されていないレコード内でユニークであることを確実にしたくなります。

　論理削除されていないレコードの範囲でユニークであることを確実にする制約は例えば次のように作れます。

-- 先ほどのusersのテーブル定義を次のSQLで書き換えます。
ALTER TABLE users
    ADD COLUMN login_id_unique VARCHAR(255) AS (IF(deleted_at IS NULL, login_id, NULL)) STORED AFTER login_id,
    ADD UNIQUE INDEX idx_login_id_unique (login_id_unique)
;
-- 書き換えると次のテーブル定義になります
create table users
(
    user_id         bigint unsigned auto_increment primary key,
    login_id        varchar(255) not null comment 'ログインID',
    login_id_unique varchar(255) as (if((`deleted_at` is null), `login_id`, NULL)) stored,
    deleted_at      datetime     null comment '削除日時',
    constraint idx_login_id_unique
        unique (login_id_unique)
) comment 'ユーザー';

　論理削除されていないならば login_id の値を、されているならば NULL の値を取るカラムである login_id_unique を追加し、login_id_unique カラムにユニーク制約をつけます。こうすると削除されていない login_id がユニークであることをデータベースが約束してくれます。