リレーションを実装する — 外部キー

ER図でテーブル間の関係を定義したら、それをSQLで実装します。リレーションを正しく実装するための仕組みが外部キー制約です。

外部キー制約とは

外部キー? 別のテーブルの主キーを参照するカラム。テーブル間の関係を表す。例：投稿テーブルの`user_id`でユーザーテーブルを参照。 は、あるテーブルのカラムが別のテーブルの 主キー? テーブルの各行を一意に識別するためのカラム。通常は自動で採番される`id`が使われる。 （または UNIQUE キー）を参照することを示します。外部キー制約を設定すると、参照整合性が保たれます。

参照整合性とは「存在しない値を参照できない」というルールです。例えば、orders.user_id = 999 というレコードを挿入しようとしたとき、users テーブルに id = 999 が存在しなければエラーになります。

CREATE TABLE orders (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  total_price INT UNSIGNED NOT NULL,
  ordered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users (id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

ON DELETE / ON UPDATE の選択

外部キー制約には、参照先が変更・削除されたときの動作を指定できます。

RESTRICT（デフォルト）

参照されているレコードの削除・変更を禁止します。

ON DELETE RESTRICT
ON UPDATE RESTRICT

使いどころ: orders.user_id → users.id のように、参照先を削除してはいけない場合。誤操作を防ぐ最も安全な設定です。

CASCADE

参照先が変更・削除されたとき、参照元も連動して変更・削除されます。

ON DELETE CASCADE
ON UPDATE CASCADE

使いどころ: order_items.order_id → orders.id のように、親レコードが消えたら子も不要になる場合。注文を削除したら注文明細も自動で削除したいときに使います。

SET NULL

参照先が削除されたとき、参照元の外部キーカラムを NULL に設定します。

ON DELETE SET NULL
ON UPDATE SET NULL

使いどころ: products.category_id → categories.id のように、カテゴリが削除されても商品は残しておきたい（カテゴリ未設定として扱う）場合。カラムは NULL を許可している必要があります。

NO ACTION

RESTRICT と似ていますが、チェックのタイミングが異なります（トランザクション終了時にチェック）。MySQL では RESTRICT と同じ動作です。

選び方のまとめ

リレーション	推奨設定	理由
orders → users	ON DELETE RESTRICT	ユーザー削除時に注文が残るのは問題
order_items → orders	ON DELETE CASCADE	注文削除時に明細も消えてよい
products → categories	ON DELETE SET NULL	カテゴリ削除でも商品は残す
reviews → users	ON DELETE CASCADE	ユーザー削除時にレビューも削除

多対多を中間テーブルで実現する

多対多? 両テーブルのある行が相手テーブルの複数行と対応する関係。「学生と科目（一人が複数科目を受講、一科目に複数の学生）」が例。中間テーブル（関連テーブル）で実現する。 の関係は、 中間テーブル? 多対多の関係を実現するために使う結合専用のテーブル。`student_courses(student_id, course_id)` のように両テーブルの外部キーを持つ。 （中間テーブル、関連テーブルとも呼ぶ）で表現します。

ECサイトの「注文」と「商品」は多対多の関係です。1つの注文に複数商品、1つの商品が複数注文に含まれます。

-- 注文テーブル
CREATE TABLE orders (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  total_price INT UNSIGNED NOT NULL,
  ordered_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
);

-- 注文明細テーブル（中間テーブル）
CREATE TABLE order_items (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity   INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price INT UNSIGNED NOT NULL,  -- 注文時の価格スナップショット
  FOREIGN KEY (order_id)   REFERENCES orders (id)   ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT,
  UNIQUE KEY uq_order_product (order_id, product_id)  -- 同じ商品を2行入れない
);

unit_price が products.price とは別に保持されている点に注目してください。注文後に商品の価格が変わっても、注文時の価格が変わらないようにするためです（スナップショットパターン）。

外部キーとパフォーマンス

外部キーはデータ整合性を保つ強力な仕組みですが、パフォーマンスへの影響も理解しておく必要があります。

自動的に作成されるインデックス

MySQL（InnoDB）では、外部キーカラムに自動的に インデックス? テーブルの検索を高速化するためのデータ構造。本の索引のように、特定の列の値から行の位置をすばやく特定できる。検索は速くなるが、書き込みはやや遅くなる。 が作成されます。これはJOINのパフォーマンスには有利ですが、インデックスのサイズ増加・書き込み時のオーバーヘッドが発生します。

INSERT / UPDATE / DELETE 時のチェック

外部キー制約があると、書き込み操作のたびに参照先の存在チェックが走ります。大量データのバッチ処理時にはこのオーバーヘッドが問題になることがあります。

大規模システムでの扱い

Google や Facebook のような超大規模システムでは、外部キー制約をあえて使わずアプリケーション側で整合性を管理するケースもあります。しかし、チームの規模が大きくなるにつれてアプリケーション側のバグリスクも高まるため、通常の業務システムでは外部キー制約を使うことを強く推奨します。

制約? カラムに設定するルール。NOT NULL（空値禁止）・UNIQUE（重複禁止）・PRIMARY KEY（主キー）・FOREIGN KEY（外部キー）・DEFAULT（デフォルト値）などがある。 の命名規則

制約に名前をつけておくと、エラーメッセージが読みやすくなります。

-- 命名例: fk_{テーブル名}_{参照先テーブル名}
CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users (id)
CONSTRAINT fk_order_items_orders FOREIGN KEY (order_id) REFERENCES orders (id)
CONSTRAINT fk_order_items_products FOREIGN KEY (product_id) REFERENCES products (id)

まとめ

• 外部キー制約は参照整合性を保証する
• ON DELETE / ON UPDATE の選択はリレーションの性質に合わせて行う
• 多対多は中間テーブルで実現する
• 外部キーはパフォーマンスにも影響するため設計時に考慮する

次回はこれまでの知識を使って、ECサイト全体のスキーマを設計する実践回です。