はじめに
この記事はSQLアンチパターンの13章、フィア・オブ・ジ・アンノウンの影響を大いに受けています。またこの記事の著者はPostgreSQLをメインで使っています。
なにはなくともNULLはつらい
値をアプリケーションで扱う場合でも、DB内のクエリで扱う場合でも、NULLというものは面倒なものです。特にクエリで扱う場合はなおさらです。
挙動が直感的ではない
select NULL || 'something';
select NULL+1;
select NULL = 'something';
これらのクエリを投げると返ってくるのは全部NULLです。
NULLチェックが大変
例えばNULLである可能性がある値をWHERE句の条件に組み込みたい場合、COALESCEやIS NOT NULLを使うなどの一工夫が必要になります。
なんにでも NOT NULL をつけることの問題
これらを嫌ってカラムに適切にNOT NULL 制約をつけることは有効です。が適切でなかった場合次にあげていくような問題が起こりえます。そしてこれらの問題は上で述べたものよりも悪質です。テーブルの例をだして説明します。
以下のようなテーブルを考えます
| 列 | 型 |
|---|---|
| tweet_id | TEXT |
| summarized_at | TIMESTAMPTZ |
| retweeted_count | BIGINT |
| impressions_count | BIGINT |
これはtwitterのあるツイートのリツイート数とインプレッション数(リツイートしてくれたユーザーのフォロワーの合計値)を1時間ごとに計測し、保存しておくためのテーブルです。
retweeted_countとimpressions_countがそれぞれ独立して集計される場合、このテーブルでNOT NULL制約をつけていいカラムはどれでしょうか？
正解は上二つです
| 列 | 型 | NOT NULL制約 |
|---|---|---|
| tweet_id | TEXT | NOT NULL |
| summarized_at | TIMESTAMPTZ | NOT NULL |
| retweeted_count | BIGINT | - |
| impressions_count | BIGINT | - |
では下二つのカラムにNOT NULL制約をつけてしまった場合、何が問題になるでしょうか?
これはretweeted_countとimpressions_countのどちらかの取得に失敗した時のことを考えればよいです。じっくり考えると失敗したときに入れられる数字が___なにもない___ことに気づくのではないでしょうか。
例えば
- 0: 本当に0のときと区別できなくなる
- 負数: 一日ごとの推移などを導出したいなどで、SUMなどの演算するときに邪魔になる
さらに言えば、今回はBIGINT型でしたが例えばTEXT型のカラムにおいて、空値用の文字を決めて使った場合でも問題があります
- 新しくプロジェクトに来た人はその代用をどう知れば良いのか？
- 代用文字で処理を分岐することはNULLチェックすることと本質的に同じではないか？
- プロジェクトにおいてNOT NULL制約そのものが信頼できなくならないか？
これらが意味することは、欠けてしまった値に入れてよいものはNULLしかないということです。
NOT NULL制約をつけるべきカラム
上で紹介したSQLアンチパターンにはこうあります
列にNOT NULL制約を宣言するのは、値のない列がその行にとって意味をなさない場合に限るべきです。例えば、Bugs.reported_by列には必ず値が必要です。すべてのバグは、誰かによって報告されたものだからです。しかしバグにまだ修正担当者が割り当てられていない場合はあります。このとき、欠けている値はNULLであるべきなのです。
これはつまりこのようなことではないでしょうか
あるカラムの値が仮に欠けてしまった時に、行全体が意味をなさなくなる場合にのみ、そのカラムにNOT NULL制約をつけるべきである。
知っているテーブル構造などを念頭に置いて考えてもらえれば、しっくりくる定義ではないでしょうか?
| 列 | 型 | NOT NULL制約 |
|---|---|---|
| tweet_id | TEXT | NOT NULL |
| summarized_at | TIMESTAMPTZ | NOT NULL |
| retweeted_count | BIGINT | - |
| impressions_count | BIGINT | - |
ただし先ほどの定義にも落とし穴があります。以下のテーブルは、先ほどから例に出しているテーブルからimpressions_countを抜き、retweeted_countにNOT NULL制約をつけたものです。この段階では定義的にもアプリケーション的にも特に問題がないことがわかると思います。
|列|型|NOT NULL制約|
|---|---|---|NOT
|tweet_id|TEXT|NOT NULL|
|summarized_at|TIMESTAMPTZ|NOT NULL|
|retweeted_count|BIGINT|NOT NULL|
ここからimpressions_countのカラムが増えるとretweeted_countのNOT NULL制約にさっき述べたような問題が起きてしまいます。先ほどの定義に従ってつけた制約が、明日も定義に従っているとは限らないわけです。
まとめ
- できればNOT NULL制約はなるべくつけたい
- NULLを他のもので代用してはいけない
- あるカラムの値が仮に欠けてしまった時に、行全体が意味をなさなくなる場合にのみ、そのカラムにNOT NULL制約をつけるべきである
- テーブルへカラムを追加することで、他のカラムのNOT NULL制約を外さないといけなくなることがある