なぜDB設計時に可能な限りのカラムにNOT NULL制約をつけるべきなのか

概要

なぜDB設計時に可能な限りのカラムにNOT NULL制約をつけるべきなのか、実際にNULLが含まれるデータを見ながら確認していきます。

NULLの種類

NULLには概念的には UNKNOWN と N/A（Not Applicable） の2種類が存在する。
N/A は適用不可のことで、VARCAHR型をINT型として評価しようとしたときなどは N/A となる。
今回確かめていく内容は、その値がなんなのかわからないことを意味する UNKNOWN についてです。

NULL（UNKNOWN）の評価

NULLとしてのUNKNOWNを評価すると真理値のunknownとなります。
つまりX=Yという式を評価するとき、例えばXが2、Yが2のとき、trueと評価できます。
また、Xが2、Yが3のときはfalseと評価できます。これは慣れ親しんだ2値論理による評価です。

これに unknown という値を加えてみます。
例えばX=unknownという式を評価してみます。Xと得体の知れないunknownという値が等価かどうか評価するため、結果は unknown となります。
これはX>unknownなどの不等号による評価でも同様なので、NULL（を評価した結果のunknown）が含まれる世界での式の評価結果はtrue/false/unknownの3値となります。

ORとAND

ORとANDについても確認してみます。
まずはORですが、ORは左辺と右辺のいずれかがtrueと評価されれば式の評価はtrueとなります。
例えばX OR unknownは、unknownがなにかわからないものの、左辺がtrueの場合は式としてはtrueになります。
左辺がunknownの場合はunknown OR unknownとなり、式の評価はunknownとなります。

続いてANDですが、ANDは左辺と右辺がいずれもtrueと評価されれば式の評価はtrueとなります。
例えばX AND unknownは、unknownがなにかわからないため、左辺がtrueであっても式の評価としてはunknownになります。
左辺がfalseの場合はfalse AND unknownとなり、式の評価はfalseになります。

なぜNULLを避けるべきなのか

実際に簡単なテーブルを作成して確認してみます。

SQL_____DDL_____CREATE TABLE Students (
  id INT AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE ExaminationResults (
  id INT AUTO_INCREMENT,
  user_id INT NOT NULL,
  mathematics INT,
  english INT,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES Students(id)
);

INSERT INTO Students (name) VALUES
('John Doe'),
('Jane Smith'),
('Bob Johnson');

INSERT INTO ExaminationResults (user_id, mathematics, english) VALUES
(1, 90, 85),
(2, 75, 88),
(3, 80, NULL);

以下のようにテーブルができます。

合計の集計

生徒の数学と英語の点数の合計を集計してみます。

SQL_____SQL_____SELECT S.name, E.mathematics + E.english AS total
FROM ExaminationResults AS E
INNER JOIN Students AS S ON E.user_id = S.id;

Bob JohnsonはNULLになってしまいました。
これはBob Johnsonの E.mathematics + E.english が 80 + unknown となり評価が unknown となってしまうためです。
2値論理的に考えれば直感的に80 + NULLは80と評価してほしいところですがそうはいかないようです。
NULLが含まれる場合は、以下のようにNULLが含まれる可能性を考慮した回避策が必要になります。

SQL_____SQL_____SELECT S.name, COALESCE(E.mathematics, 0) + COALESCE(E.english, 0) AS total
FROM ExaminationResults AS E
INNER JOIN Students AS S ON E.user_id = S.id;

NOT IN

続いて、NOT INについて以下のテーブルを作って確認していきます。

SQL_____DDL_____CREATE TABLE Table1 (
  x INT NOT NULL
);

CREATE TABLE Table2 (
  y INT
);

INSERT INTO Table1 (x) VALUES (1), (2), (3), (4);

INSERT INTO Table2 (y) VALUES (1), (2), (NULL), (NULL);

NOT INを使ってみます。

SQL_____SQL_____SELECT * FROM Table1
WHERE x NOT IN (SELECT y FROM Table2);

text_____Result_____Record count: 0;

2値論理的な直感では 3, 4 という結果を期待しましたがNULLが含まれていることによってそうなりませんでした。

NOT EXISTS

NOT EXISTSについては以下のテーブルを作って確認してみます。

SQL_____DDL_____CREATE TABLE Employees (
  name VARCHAR(32) NOT NULL,
  department VARCHAR(32) NOT NULL
);

CREATE TABLE Managers (
  name VARCHAR(32) NOT NULL,
  department VARCHAR(32)
);

INSERT INTO Employees (name, department) VALUES
('John Doe', 'Sales'),
('Jane Smith', 'Marketing'),
('Bob Johnson', 'Engineering');

INSERT INTO Managers (name, department) VALUES
('Alice Johnson', NULL),
('Tom Smith', 'Sales'),
('Charlie Brown', 'Marketing');

Managersテーブルのdepartmentの値に存在しないEmployeesテーブルのdepartmentの値をSELECTしてみます。

SQL_____SQL_____SELECT E.name
FROM Employees AS E
WHERE NOT EXISTS (
	SELECT *
	FROM Managers AS M
	WHERE E.department = M.department
);

今度は2値論理的にも直感的な値を得られてしまいました。
何が起きたかわかりやすく M.department の部分を NULL に変更して再度実行してみます。

SQL_____SQL_____SELECT E.name
FROM Employees AS E
WHERE NOT EXISTS (
	SELECT *
	FROM Managers AS M
	WHERE E.department = NULL
);

上記のクエリでは、サブクエリでのNULLとの比較結果が常に unknown となって1レコードも選択されずサブクエリが結果を何も返さないので、何もないものに対してNOT EXISTSを評価し、結果として全レコードが選択されています。

まとめ

今回はいくつかのパターンでNULLが含まれる場合には3値論理となり、直感的な結果ではなくなってしまうことがわかりました。
期待しないことが起きやすくなるのでDB設計時には可能な限りのカラムにNOT NULL制約をつけるべきだという理解をしました。
クエリが過度に重くなるなどのパフォーマンス的な問題が起きない限り、DB設計時にはNOT NULL制約をつけていこうと思います。

参考

以下のページを参考に自分でもクエリを書いてみて検証しました。
3値論理 ―― 神のいない論理