こんにちは、Tochiです。 マジで知らんかった！となった話を共有です。
NULLはユニーク制約に縛られない！！！
「NULL」はユニーク制約に縛られません。 例えば、以下のような制約があったとしてます。
CREATE UNIQUE INDEX clients_on_organization_id__identifier__deleted_at ON public.clients USING btree (organization_id, identifier, deleted_at)
このとき、下記のようなレコードは存在しうるでしょうか？
| organization_id | identifier | deleted_at |
|---|---|---|
| 1 | xxxxx | NULL |
| 1 | xxxxx | NULL |
もちろん、ユニーク制約がかかるので違反状態にな......りません！！！
実はこのレコードは存在しうるのであります！！！
DBMSによってNULLの概念が違う
実はDBMSによってNULLの概念が違います。
「NULLはNULLに一致しない」というのが大原則です。 例えば、下記のSQLはWHRE句がfalseになるので何も表示されません
SELECT * FROM users c WHERE null = null
そのため、PostgreSQLやMySQLではこの考えのもと、 NULL同士は違う値として認識されるので、先に述べた例はユニーク制約に引っかからないのです。
え、でもそうじゃないDBMSは？
そうではないDBMSもあると述べました。 例えば、SQL Serverがそうです。 これらのDBMSには「重複」という概念が別で存在します。
つまり「NULLとNULLは一致はしないが重複はする」というルールのもとうまいこと成り立っているわけです。
まとめ
今回、ユニーク制約がDBMSによって異なるということを発見しました。 多分、これを知っているだけでもかなり多くの人が助かるような気がします。
お役に立てるといいな、、
