ある条件下でユニークな制約を実現する
設計としてどうなの？ という気もしますが、たとえばアプリケーション上はキーとなるカラムがあるがテーブルの設計上は同じ値で複数行の挿入ができるようにしたい。でも、そのキーの値はある条件下ではユニークであることを保証したい場合。
具体的な例で言うと、
| カラム名 | 制約 |
|---|---|
| シーケンス番号 | 主キー |
| 登録番号 | NOT NULL |
| 開始日 | NOT NULL |
| 完了日 |
みたいなテーブル設計にして、アプリケーションは最初にこのテーブルに登録番号と開始日を挿入する。次に更新完了日がNULLの行を抽出して処理を行ったあとに完了日を更新するという動作を想定します。
この時に、更新完了日がNULLである行だけを抽出したら登録番号はユニークであって欲しいとします。
これをPostgreSQLで実現するためには、
| カラム名 | 制約 |
|---|---|
| シーケンス番号 | 主キー |
| 登録番号 | NOT NULL |
| 開始日 | NOT NULL |
| 完了日 | |
| フラグ |
という風にフラグというカラムを足して、これにUNIQUE(登録番号,フラグ)のUNIQUE制約とCHECK(フラグ = TRUE AND 完了日 IS NULL OR フラグ IS NULL)のCHECK制約をつけます。（フラグ IS NULLの時には完了日はNOT NULLにしたかったが、うまくいかず。CHECK制約ではNULLは制限されないから？）
今回はフラグはboolean型ですが、これで完了日がNULLの場合はフラグがTRUEであるという実装*1をすれば、間接的ですが、完了日がNULLである行（フラグがTRUEの行）*2だけ抽出した場合の登録番号のユニークが保証できます。
これはPostgreSQLが標準SQLに準拠し、UNIQUE制約でもNULLは複数格納できるため、逆にUNIQUE制約を有効にしたい場合のみフラグに値をいれるということです。
https://www.postgresql.jp/document/9.3/html/ddl-constraints.html
