女性向けエンジニアスクール
3秒でわかる
別テーブルの主キーを指す列に付ける制約のこと。存在しない相手を参照する行が紛れ込むのを、データベースの側で確実に防げます。
もう少し詳しく
どういうものか
外部キーは、あるテーブルの列が別のテーブルの主キーを指していることをデータベースに宣言する制約です。注文テーブルの user_id に外部キーを付けると、ユーザーテーブルに存在しないIDの注文は登録できなくなります。参照先が消えるときの動きも指定でき、一緒に消す、NULL にする、参照が残っていれば削除を拒む、といった選択があります。
なぜ必要か
参照先が無い行は、アプリから見ると「ユーザー名が表示できない注文」として現れます。こうした行は、集計の件数を狂わせ、画面ではエラーになります。アプリ側の検証だけで防ごうとしても、バッチ処理や手作業のSQL、別サービスからの書き込みなど、入口はいくつもあります。データベース側に置いた制約はすべての入口に効きます。表どうしの関係が定義として残るため、あとから設計を読み解く人への説明にもなります。
具体例
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
total INT NOT NULL,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE RESTRICT
);
INSERT INTO orders VALUES (1, 999, 1200); -- users に 999 が無ければ失敗するつまずきやすいところ
参照する側の列に索引が無いまま運用すると、親テーブルの削除や更新のたびに子テーブルを全走査することになり、行が増えたときに急に遅くなります。MySQLは自動で作りますが、そうでない製品もあるので確認します。もうひとつ、ON DELETE CASCADE の指定は便利に見えて危険です。ユーザーを1人消しただけで注文履歴が丸ごと消え、売上の集計から欠けます。消さずに退会フラグを立てる設計のほうが安全な場面が多くあります。大量のデータを一括投入するあいだだけ制約を外す運用も、戻し忘れると参照先の無い行が静かに残ります。外すなら、元に戻す作業まで手順書に書いておきます。
似た用語との違い
| 語 | 役割 |
|---|---|
| 主キー | その表の行を一意に決める列 |
| 外部キー | 別の表の主キーを指す列と、その制約 |
| 一意制約 | 重複を禁じるだけで参照は伴わない |
覚え方
主キーが名札なら、外部キーはその名札を指す矢印です。矢印の先が必ず存在することをデータベースに約束させる仕掛け、と捉えます。
