PostgreSQLの検査制約におけるNULLの扱い

PostgreSQLを基準とした資格試験の問題集「徹底攻略 OSS-DB Silver問題集［OSDBS-01］対応」にて、以下の検査制約を設定したテーブルに対して、値にNULLを含んだINSERTが成功するか？という問題がありました。

CREATE TABLE points (
  col1 INTEGER PRIMARY KEY,
  col2 INTEGER,
  CHECK (
  col1 > 0
  AND col2 < 0
  )
);

INSERT INTO points
VALUES
  (3, NULL);

答えは「成功する」なのですが、これを理解するには検査制約におけるNULLの扱いがポイントとなります。
本エントリで解説します。

NULLと比較述語について「達人に学ぶSQL徹底指南書」の「1-3 3値論理とNULL」からまとめます。

NULLに比較述語を適用した結果は常に真理値unknownとなります。これはNULLが値でも変数でもなく、値を対象とする比較述語を適用できないためです。

これに伴い、以下の式は全部unknownとなります。
1 = NULL
1 > NULL
1 < NULL
1 <> NULL
NULL = NULL

真理値には以下の優先順位があります。
ANDの場合：false > unknown > true
ORの場合：true > unknown > false
"true AND unknown"なら結果はunknownです。
"true OR unknown"なら結果はtrueです。

前節で説明したNULLと比較述語の関係はPostgreSQLについても成り立ちます。

入力のどちらかがNULLの場合、通常の比較演算子は真や偽ではなく（「不明」を意味する）nullを生成します。例えば7 = NULLはnullになります。7 <> NULLも同様です。
9.2. 比較関数および演算子

そして、検査制約のNULLの扱いについて以下の説明をしています。

検査制約では、検査式が真またはNULL値と評価された場合に、条件が満たされることに注意して下さい。ほとんどの式は、演算項目に一つでもNULLがあればNULLと評価されるので、検査制約では制約対象の列にNULL値が入るのを防げません。
5.3. 制約

冒頭のSQLを再掲します。

CREATE TABLE points (
  col1 INTEGER PRIMARY KEY,
  col2 INTEGER,
  CHECK (
  col1 > 0
  AND col2 < 0
  )
);

INSERT INTO points
VALUES
  (3, NULL);

INSERT実行時、検査式は以下の通り評価されます。
(3 > 0) AND (NULL < 0)
= true AND null
= null

PostgreSQLは検査式がNULLと評価された場合も条件を満たすため、このINSERTは成功します。

PostgreSQLで列にNULL値を含ませないためには非NULL制約(NOT NULL)が推奨されています。

非NULL制約は常に列制約として記述されます。非NULL制約はCHECK (column_name IS NOT NULL)という検査制約と機能的には同等ですが、PostgreSQLでは、明示的に非NULL制約を作成する方がより効果的です。
5.3. 制約

SQLを書くときに常に手元へ置いておきたい一冊。
こんな時はどうなるのだっけ？というヒントがまとめられています。

この問題集を解くことで、今回のエントリのようにわかっているようでわかってないことに気づくチャンスとなるかも。

ぱと隊長日誌