【小ネタ】PostgreSQLで特定のカラムにユニーク制約を貼りたい！だが、論理削除したデータは除外したい

はじめに

例えば以下のようなadministrators(管理者)テーブルがあるとします。
また、usernameとpasswordはログインに利用します。
そして管理者の削除は、物理削除ではなく、論理削除で行います。

カラム名	型	NOT NULL
id	integer	YES
username	varchar(255)	YES
password	varchar(255)	YES
deleted_at	timestamp

この場合、usernameはログインIDの役割を果たすのでにユニーク制約を貼ることになるかと思いますが、
以下のように張った場合、問題が発生します。

CREATE UNIQUE INDEX
  administrators_username_unique ON
  administrators (username);

管理者Aとして以下のデータを登録した後に、

カラム名	値
id	1
username	adminA
deleted_at	NULL

管理者Bとして以下のデータを登録しようとするとエラーになるには当たり前ですが、

カラム名	値
id	2
username	adminA
deleted_at	NULL

管理者Aを以下のように論理削除したとしても、管理者Bの登録はエラーになってしまいます。
これだと、削除の意味がありませんね

カラム名	値
id	1
username	adminA
deleted_at	2025-05-02 12:00:00

じゃあ、複合ユニークにすればいいのでは？

論理削除データは除外したいんなら、deleted_atカラムも見るように複合カラムでユニークキーを貼ればいいのでは？と思ったりもするかと思います。

CREATE UNIQUE INDEX
  administrators_username_unique ON
  administrators (username, deleted_at);

しかし実は、UNIQUE INDEXは、デフォルトではNULLを区別しないため、今度は同じusernameの管理者データが複数登録できてしまいます。

NULLS NOT DISTINCTの登場

そこで、PostgreSQL15から使えるようになったNULLS NOT DISTINCTの出番です。
以下のように制約を貼ることで、usernameの重複は許さない、かつ、論理削除されたデータは除外ということが出来るのです！！！

CREATE UNIQUE INDEX
  administrators_username_unique ON
  administrators (username, deleted_at) NULLS NOT DISTINCT;

ちなみに、エラーとしてはこんな感じです。

ERROR:  duplicate key value violates unique constraint "administrators_username_unique"
DETAIL:  Key (username, deleted_at)=(adminA, null) already exists.