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

More than 1 year has passed since last update.


論理削除

アプリケーション上でなにかエントリ(例えば記事だとかユーザだとか)を削除したとき、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 の改題再編集です)