Day5 後半のゴール
「PRIMARY KEY と FOREIGN KEY が“実際にどう効くか”を体で理解する」
前半で役割はイメージできたので、後半は「わざと失敗させてみる」フェーズです。
PRIMARY KEY と FOREIGN KEY を付けたテーブルを作り、あえて変なデータを入れようとして、MySQL に怒られます。
この「怒られ方」を知っておくと、実務でエラーを見たときにすぐ原因にたどり着けるようになります。
例題：ユーザーと注文テーブルをちゃんと定義する
「PRIMARY KEY と FOREIGN KEY をフル装備した基本形」
まずは、典型的な「users と orders」の2テーブルを作ります。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount INT NOT NULL,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
) ENGINE=InnoDB;
ここで押さえたいのは次の3点です。
users.id は PRIMARY KEY（AUTO_INCREMENT）
orders.id も PRIMARY KEY（AUTO_INCREMENT）
orders.user_id は users.id への FOREIGN KEY
つまり、「注文は必ず既存ユーザーに紐づく」というルールを、DBに宣言しています。
正常パターン：正しい順番で INSERT する
「親 → 子 の順で入れると、何も問題は起きない」
まずは、正しい流れでデータを入れてみます。
INSERT INTO users (name)
VALUES ('山田太郎'), ('佐藤花子');
この時点で、users はこうなっているはずです。
SELECT * FROM users;
-- id=1 山田太郎
-- id=2 佐藤花子
次に、orders に注文を入れます。
INSERT INTO orders (user_id, amount)
VALUES
(1, 5000),
(2, 3000);
これは普通に通ります。
FOREIGN KEY 的にも、「user_id=1,2 は users.id に存在する」ので問題なしです。
ここまでは SQLite とほぼ同じ感覚ですが、ここから「わざと壊しにいく」と差が見えてきます。
異常パターン1：存在しない user_id を入れてみる
「FOREIGN KEY が“おかしな参照”をブロックする瞬間」
今度は、存在しないユーザーIDで注文を作ろうとしてみます。
INSERT INTO orders (user_id, amount)
VALUES (999, 1000);
users テーブルには id=999 のユーザーはいません。
このとき、MySQL はどうするか。
InnoDB＋FOREIGN KEY が有効なら、エラーになります。
内容としては「外部キー制約違反（foreign key constraint fails）」という趣旨です。
ここで大事なのは、
アプリ側で「999 が存在するか」をチェックしていなくても、
DBが「そんなユーザーはいない」と拒否してくれる
ということです。
SQLite で FOREIGN KEY を使っていなかった場合、
このような「おかしな注文」が普通に入ってしまっていたかもしれません。
MySQL では、DBが“最後の番人”になってくれます。
異常パターン2：親ユーザーを消そうとしてみる
「“子がいる親”を削除しようとするとどうなるか」
次は逆方向です。
すでに注文を持っているユーザーを削除しようとしてみます。
DELETE FROM users
WHERE id = 1;
id=1 のユーザーには、orders に紐づく行が存在しています。
このとき、FOREIGN KEY があると、MySQL はこれもエラーにします。
「まだこのユーザーを参照している注文があるから、消しちゃダメ」
というわけです。
これがないと、
users からは消えたのに、orders には user_id=1 の注文が残る
→ 「親のいない子ども（孤児レコード）」が生まれる
という状態になります。
FOREIGN KEY は、この「孤児」をDBレベルで禁止してくれます。
ON DELETE の挙動を知る
「“親が消えたとき、子をどうするか”を設計で決められる」
FOREIGN KEY には、「親が消されたときどうするか」を指定するオプションがあります。
代表的なのが ON DELETE RESTRICT（デフォルト）と ON DELETE CASCADE です。
デフォルト（RESTRICT 相当）
親を消そうとすると、子が残っていればエラーにする
CASCADE
親を消したら、その親を参照している子も自動で消す
例えば、orders をこう定義するとします。
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount INT NOT NULL,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
) ENGINE=InnoDB;
この場合、
DELETE FROM users WHERE id = 1;
を実行すると、
users.id=1 の行と、それに紐づく orders の行がまとめて消えます。
ここでの設計ポイントは、
ユーザーを消したら、その人の注文も一緒に消えてよいのか
それとも、「注文は履歴として残したい」からエラーにすべきか
というビジネスルールです。
FOREIGN KEY は、「関係が壊れないこと」を保証しつつ、
「壊そうとしたときの挙動」も設計で選べる仕組みになっています。
SQLite との違いをもう一度整理する
「“あったら便利”から“使う前提”へ」
SQLite でも、FOREIGN KEY 自体は書けましたが、
PRAGMA foreign_keys = ON; を毎回設定しないと効かない
アプリ側がそもそも使っていないことも多い
という「オプション扱い」になりがちでした。
MySQL＋InnoDB では、
FOREIGN KEY を前提にテーブル設計をする
→ DBが常に整合性をチェックしている
という世界観です。
この差は、長期運用になればなるほど効いてきます。
アプリのバグや一時的な不具合があっても、
DBが「おかしなデータ」を中に入れないよう守ってくれる。
結果として、後からの調査・修正コストが大きく下がります。
セキュリティ・品質の観点から見た PRIMARY / FOREIGN KEY
「“信頼できるデータ”を作るための最低ライン」
PRIMARY KEY と FOREIGN KEY は、
単に「SQLの機能」ではなく、「信頼できるデータベース」を作るための最低ラインです。
PRIMARY KEY
内部用の連番IDを主キーにすることで、
メールアドレスや会員番号など“意味のある値”を主キーにせずに済み、
漏えい時のリスクや仕様変更時の影響を小さくできる。
FOREIGN KEY
存在しないIDを参照するデータや、
親が消えたのに子だけ残るデータをDBレベルで拒否できる。
アプリのバグや不正リクエストがあっても、
DBが「それはおかしい」と言ってくれる“最後の防波堤”になる。
SQLite経験者としては、
「今までアプリ側で頑張っていたチェックを、MySQLではDBに任せられる」
と捉えると、FOREIGN KEY を使うモチベーションが一気に上がるはずです。
Day5 後半のまとめ
users と orders のような親子テーブルに PRIMARY KEY と FOREIGN KEY を設定すると、「存在しない user_id で注文を作ろうとしたとき」「注文が残っているユーザーを削除しようとしたとき」に、MySQL が外部キー制約違反としてエラーを返し、“おかしな参照”や“孤児レコード”をDBレベルで防いでくれる。
FOREIGN KEY には ON DELETE などのオプションがあり、親を削除したときに子も連動して消す（CASCADE）か、子がいる限り親の削除を禁止する（RESTRICT相当）かを設計で選べるため、「履歴を残したいのか」「一緒に消してよいのか」といったビジネスルールをDBに埋め込める。
SQLiteではFOREIGN KEYが“あれば便利なオプション”になりがちだったのに対し、MySQL＋InnoDBでは「PRIMARY KEY＝内部用連番ID」「FOREIGN KEY＝テーブル間の整合性ガード」を前提に設計することで、データの信頼性・セキュリティ・運用のしやすさが大きく向上する。
ここまで腹落ちしていれば、
「とりあえずテーブルを作る」段階から一歩進んで、
「壊れにくいテーブル設計」を意識できるようになっています。