読者です 読者をやめる 読者になる 読者になる

ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

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と比較述語

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です。

PostgreSQLの仕様

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

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

9.2. 比較関数および演算子

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

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

5.3. 制約

検査制約でNULLを評価する

冒頭の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は成功します。

非NULL制約(NOT NULL)

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

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

5.3. 制約

参考

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

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

徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応 (ITプロ/ITエンジニアのための徹底攻略)

徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応 (ITプロ/ITエンジニアのための徹底攻略)

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