回答。

質問の背景

ビジネスキーの一意性の制御（IDだけでなく「自然」のフィールドに基づくもの）は、企業アプリケーションでは長い間重要な課題です。これらのキーは複雑な場合が多く（複数の列から構成される）、NULL値を許可します。標準的なSQL手段であるUNIQUE CONSTRAINTや一意のインデックスには制限があります：SQL標準によれば、UNIQUEキーの構成要素のうち少なくとも1つがNULLである複数の行は一意と見なされ、制約を違反しません。

問題

ビジネスロジックによっては、NULLを含む列のセット（複合）が一意であることが要求される場合がありますが、SQL標準の挙動（NULL ≠ NULL）がこのシナリオを壊します。たとえば、私たちのテーブルには(passport_number, passport_series)という2つの列がありますが、少なくとも1つがNULLであっても、値が一致する場合は重複を許可しない必要があります。

解決策

ほとんどの一般的なDBMSでは、ユニーク性を制御するためにトリガーを実装するか、条件付きの部分インデックスを使用するか、NULLを等しい値として比較できる関数（たとえば、ISNULLまたはCOALESCE）を利用します。以下は、PostgreSQLでの式に基づく一意のインデックスの例です：

CREATE UNIQUE INDEX idx_passport_unique
ON persons (
  COALESCE(passport_number, ''),
  COALESCE(passport_series, '')
);

アプリケーションのロジックレベルでは、挿入段階で重複を見逃さないように、チェックを重複させることがよくあります。

主な特徴：

標準のUNIQUE CONSTRAINTは、キーにNULLが含まれる場合には機能しません。
解決策はDBMSによって異なります：部分インデックス、計算列、トリガーが使用されます。
適切なインデックスを作成できない場合、ユニーク性のチェックはビジネスロジックに依存します。

トリッキーな質問。

NULLを含む列がある場合、ユニークインデックスが重複の挿入を不可能にすることを保証できますか？

いいえ、できません。SQLではNULLの扱いが特異で、少なくとも1つがNULLの同じ値セットを持つ行は異なると見なされ、同時に保存されることが許可されます。

異なるDBMSがNULLの一意性を異なる方法で実装することがありますか？

はい、彼らの間には違いがあります。たとえば、OracleではユニークインデックスがNULLを含む複数の行を許可しますが、MS SQLでは1行しか許可されません。PostgreSQLでは、式を使用して部分インデックスを作成できます。

DDLレベルだけで、トリガーなしでこの問題を回避することは可能ですか？

いくつかのDBMSでは、COALESCEなどの式を使って可能です。しかし、常にそうとは限らず、複雑な動作が必要な場合（NULLを重複と見なす）には、論理チェックやトリガーを使用する必要があります。

一般的な間違いとアンチパターン

列にNULLが許可される場合、標準のUNIQUEに依存する
ビジネスロジックレベルで重複を確認しない
パフォーマンスを考慮せずにトリガーを作成する

実際の例

ネガティブケース

クリティカルでないデータベースでは、(email, 部署コード)に基づくユニークインデックスに依存することが決定され、両方のフィールドがNULLを許可しました。その結果、重複が何度も発生し、外部統合が壊れました。

利点：

シンプルなDDL

欠点：

明白でない重複、一貫性のないデータ、レポートのエラー

ポジティブケース

PostgreSQLでは、(パスポート番号、シリーズ)のビジネスキーをCOALESCEによるインデックスで実装し、さらにアプリケーション側でのチェックを追加しました。