論理削除と一意性制約を両立させる方法・DB製品別

論理削除

アプリケーション上でなにかエントリ（例えば記事だとかユーザだとか）を削除したとき、DB上の行は削除せず単に【削除済み】フラグを立てるだけという扱い方を 論理削除 と呼びます。
論理削除にはいろいろなメリットがあります。行削除のように関連する他テーブルへ削除が波及しないこと、エントリ復活ができること、障害時にデータ変更の経緯を追いやすくなることなどなど（デメリットもわんさかあるんですが、この記事の主旨からははずれるので別途お調べください）。

ところが論理削除の方針でDBを組んでいて困ったことはありませんか？
「 メールアドレスは一意性(UNIQUE)制約をかけたいのに、それだと削除済みのユーザと同じメールアドレスが使えないことになる 」

論理削除と一意性制約、両立はできないのか？
できないと思っている方、多いと思います。実はちゃんとできます。DB製品によって実現方法がちょっと違ってくるだけで。

部分インデックス（SQL Server, PostgreSQL）

SQL Server, PostgreSQLの二製品は、テーブルの一部の行だけインデックスの対象にできます。 部分インデックス （SQL Serverでは インデックスフィルタ ）と呼ばれる機能です。
もちろんこれはユニークインデックスでも使えるので、制約の代わりにユニークインデックスを作成することで実現できます。

CREATE UNIQUE INDEX mail_is_unique ON users (mail)
WHERE status = 0;

ここでは、statusカラムが0なら健在ユーザです。WHERE句で対象行の条件を書くだけのシンプルな記法ですね。

NULLが一意性判定から除外されることを利用（MySQL, Oracle）

MySQL, Oracleには部分インデックス機能がありません。しかし、一意性制約がNULLを対象外にする性質を利用すればやはり両立が可能です（この表技的な手法はSQL Serverでは逆に使えません。SQL ServerがSQL標準に反してNULLを判定対象に含めてしまうからです）。

コツは、削除済みであることをstatusカラムがNULLであることで表現することです。そのstatusカラムはチェック制約によりNULLか0かしかとれません。

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY,
    mail VARCHAR NOT NULL,
    status INT CHECK (status = 0),
    CONSTRAINT mail_is_unique UNIQUE (mail, status)
);

アドレスとstatusの組をキーに一意性制約をかけます。削除済みユーザはNULLがキーに含まれるので一意性の判定対象にならないという次第。表技感あふれてますね。

いいプログラムはいいデータ制約から

制約はお好きですか？
おれは大好きです！

（本人のブログ記事 http://cs.hatenablog.jp/entry/2013/07/02/122318 , http://cs.hatenablog.jp/entry/2013/08/21/200809 の改題再編集です）