外部キーの役割
存在しない著者の本が、黙って登録される
テーブルを 2 つ作ります。著者を持つ authors と、本を持つ books です。books.author_id には authors.id を入れる、という約束にしました。
SQL クエリ
CREATE TABLE authors (id INT PRIMARY KEY, name TEXT);
CREATE TABLE books (id INT PRIMARY KEY, author_id INT, title TEXT);
INSERT INTO authors VALUES (1, '夏目');
INSERT INTO books VALUES (10, 1, 'こころ');
INSERT INTO books VALUES (11, 99, '幻の一冊'); -- 99 番の著者は居ない最後の INSERT は通ります。約束はチームの頭の中にあるだけで、DB は知らないからです。こうして生まれた author_id = 99 の行を孤児レコードと呼びます。
孤児は静かに悪さをします。著者名を添えて一覧を出すと、この本だけが結果から抜け落ちます。本の総数と、著者ごとに数えた合計が合わなくなります。しかも画面から消える方向に壊れるので、見ていて気づけません。異変に気づくのはたいてい、月末に数字が合わないときです。
親に無い値は、入れさせない
FOREIGN KEY を宣言すると、この約束を DB 自身が見張るようになります。
SQL クエリ
CREATE TABLE books (
id INT PRIMARY KEY,
author_id INT REFERENCES authors(id),
title TEXT
);
INSERT INTO books VALUES (11, 99, '幻の一冊');
-- ERROR: foreign key constraint violationアプリ側の検査を書き忘れても、手打ちの INSERT でも、取り込みバッチでも、同じように弾かれます。子が持つ値が必ず親に存在する、この状態を参照整合性と呼びます。
宣言は必ず子側に書く
diagram (will load when visible)
値の正解を持っている側が親、それを借りる側が子です。REFERENCES を書くのは常に子のほうで、親の定義には何も足しません。知らないテーブル定義を渡されたときも「REFERENCES を持っている側が子」と読めば、関係の向きで迷いません。
宣言するときの前提は 2 つあります。参照される親の列が PRIMARY KEY か UNIQUE であること、そして親と子で列の型を揃えることです。INT と BIGINT を混ぜると、宣言できないか、できても比較のたびに変換が挟まります。
テーブル構造
CREATE TABLE customers (
id INT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
total INT NOT NULL
);
INSERT INTO customers (id, name) VALUES
(1, '田中'),
(2, '佐藤'),
(3, '鈴木');
INSERT INTO orders (id, customer_id, total) VALUES
(101, 1, 3000),
(102, 2, 5000),
(103, 1, 1500),
(104, 99, 8000); -- 99番の顧客は存在しない (orphan)
期待される出力
| id | name | total |
|---|---|---|
| 101 | 田中 | 3000 |
| 102 | 佐藤 | 5000 |
| 103 | 田中 | 1500 |