MySQLの主キー・外部キー設計：自然キーとサロゲートキーの基本

データベース設計の要となる主キー（PRIMARY KEY）と外部キー（FOREIGN KEY）は、テーブル間の整合性を支える重要な制約です。主キーの選び方として「自然キーとサロゲートキーの違い」を整理し、外部キー制約の効果、ON DELETE / ON UPDATE の各オプションの挙動を実例で解説します。テーブル設計の段階で押さえておくべき判断基準を、まとめて確認できます。

このシナリオで考える

EC サイトの3テーブル構成を例として使います。ユーザー（users）が注文（orders）を行い、注文には複数の明細行（order_items）が紐づく、というよくある設計です。

CREATE TABLE users (
  user_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email     VARCHAR(255) NOT NULL UNIQUE,
  name      VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  ordered_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

CREATE TABLE order_items (
  item_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   INT UNSIGNED NOT NULL,
  product    VARCHAR(200) NOT NULL,
  quantity   INT          NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

このスキーマを軸に、主キーと外部キーの各概念を順番に見ていきます。

PRIMARY KEY（主キー）の基本と設計判断

主キーは「テーブル内で行を一意に特定するための列（または列の組み合わせ）」です。MySQL の InnoDB では、主キーはクラスタインデックス（テーブルの物理的な並び順）として機能します。

主キーには次の制約が自動的に課されます。

• NOT NULL：NULL 値は持てない
• UNIQUE：重複値は持てない
• テーブルにつき1つだけ定義できる

自然キーとサロゲートキーの違い

主キーの候補として「自然キー」と「サロゲートキー」の2種類があります。

種類	説明	例	主なデメリット
自然キー	業務上の意味を持つ既存の値	メールアドレス、社員番号、ISBN	変更が発生すると外部キーへの影響が大きい
サロゲートキー	業務的意味のない連番や UUID	AUTO_INCREMENT の整数、UUID	業務的な一意性は別途 UNIQUE 制約で担保が必要

メールアドレスを主キーにすると、ユーザーがアドレスを変更したとき参照している全テーブルへの更新が必要になります。サロゲートキー（user_id のような連番）を使えば、変更の影響範囲を最小限に抑えられます。

実務ではサロゲートキーを主キーに、自然キーは UNIQUE 制約で守るパターンが広く採用されています。

CREATE TABLE users (
  user_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- サロゲートキー
  email    VARCHAR(255) NOT NULL UNIQUE,              -- 業務的な一意性はここで保証
  name     VARCHAR(100) NOT NULL
);

AUTO_INCREMENT と UUID の選び方

サロゲートキーの実装として、AUTO_INCREMENT と UUID の2択がよく議論されます。

	AUTO_INCREMENT (INT/BIGINT)	UUID (VARCHAR/BINARY(16))
発行速度	速い（DB が連番を生成）	やや遅い（UUID 生成・格納サイズが大きい）
索引性能	高い（単調増加でページ分割が少ない）	ランダム値で B-tree が断片化しやすい
外部公開時	連番が推測されやすい（ID=1, 2, 3…）	推測困難
分散システム	DB サーバーが単一採番ポイントになる	複数 DB でも衝突しない

単一 DB の一般的なアプリケーションなら INT UNSIGNED AUTO_INCREMENT が性能とシンプルさのバランスで最良です。行数が 21億を超える見込みなら BIGINT UNSIGNED を選びましょう。

FOREIGN KEY（外部キー）の基本と ON DELETE / ON UPDATE オプション

外部キー制約は「子テーブルの列値が、必ず親テーブルの特定列に存在すること」を DB レベルで強制する仕組みです。アプリケーション側での整合性チェックに頼らず、DB 自体が不整合を弾いてくれます。

-- orders.user_id は必ず users.user_id に存在する値でなければならない
FOREIGN KEY (user_id) REFERENCES users(user_id)

この制約がある状態で次の操作を試みるとエラーになります。

• 存在しない user_id を持つ注文を INSERT しようとする
• 注文が残っているユーザーを DELETE しようとする（デフォルトは拒否）

ON DELETE / ON UPDATE オプションの一覧

親テーブルの行を削除・更新したときの子テーブルへの影響を、ON DELETE と ON UPDATE で制御します。

オプション	親の変更時の子の動作	使いどころ
RESTRICT（省略時デフォルト）	子が存在する場合はエラーで拒否	誤削除を防ぎたいとき（ユーザー削除など）
NO ACTION	RESTRICT と同じ（MySQL では同一動作）	RESTRICT の明示的な代替表記
CASCADE	子も連動して削除・更新される	明細行など、親と一緒に消えてよいデータ
SET NULL	子の外部キー列を NULL に更新	担当者削除時に「担当なし」にしたいとき

先ほどのスキーマでは orders → users を ON DELETE RESTRICT（ユーザーを誤って消せないよう保護）、order_items → orders を ON DELETE CASCADE（注文を消したら明細も一緒に削除）としています。この組み合わせは EC サイトで典型的なパターンです。

-- 担当者（staff）が削除されたら、tasks の担当者は NULL にする例
CREATE TABLE tasks (
  task_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  staff_id   INT UNSIGNED,  -- NULL を許容する
  title      VARCHAR(200) NOT NULL,
  FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

よくある落とし穴と注意点

InnoDB でないと外部キーが無効になる

外部キー制約は InnoDB ストレージエンジンのみサポートしています。ENGINE=MyISAM でテーブルを作成すると、構文は通っても制約が実際には機能しません。MySQL 8.0 のデフォルトは InnoDB なので新規作成なら問題ありませんが、古いテーブルを引き継ぐ際は確認しましょう。

-- エンジンを確認する
SELECT ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'mydb'
  AND  TABLE_NAME   = 'orders';

外部キー列には自動でインデックスが作られる

InnoDB は外部キーを定義すると、その列に自動でインデックスを作成します。親テーブルの行を削除・更新する際に子テーブルを高速検索するためです。意識していなくてもインデックスは存在するため、SHOW INDEX FROM orders; で確認できます。

CASCADE の連鎖に注意する

CASCADE は便利ですが、設定を誤ると意図しない大量削除につながります。「users を1件削除 → orders が消える → order_items も全消え」という連鎖が起きる可能性があります。テーブルが深く繋がっている場合は、CASCADE の連鎖経路を設計段階で図に書いて確認しましょう。

大量ロード時のパフォーマンス

INSERT / UPDATE / DELETE の度に親テーブルへの整合性チェックが走るため、書き込みが多いテーブルではわずかなオーバーヘッドが生じます。大量一括ロードの際は SET FOREIGN_KEY_CHECKS = 0; でチェックを一時的に無効化することもありますが、ロード後に必ず SET FOREIGN_KEY_CHECKS = 1; で有効化し、整合性を確認してください。

まとめ

主キーはサロゲートキー（AUTO_INCREMENT）を使い、業務的な一意性は UNIQUE 制約で別途保証するパターンが実務の主流です。外部キーは DB レベルで参照整合性を強制でき、ON DELETE / ON UPDATE で親の変更時の振る舞いを細かく制御できます。RESTRICT で誤削除を防ぎ、CASCADE で親子を一緒に削除するという2つの使い分けを押さえれば、大半のケースに対応できます。

参考リンク

• MySQL 8.0 Reference: FOREIGN KEY Constraints
• MySQL 8.0 Reference: PRIMARY KEY and UNIQUE Index Constraints

アイキャッチ画像: Photo by Hal Gatewood on Unsplash