【SQL】主キー・外部キーの役割と設定方法

SQL
2021.12.28

こんにちは、かつコーチです。

前回はデータ型の基本を解説し、テーブルの列にどんな型を設定すべきかを見てきました。

これまでの記事で何度か登場していたPRIMARY KEYという言葉を、今回はしっかり掘り下げます。

あわせて、複数のテーブルを結びつける外部キーについても解説します。

主キー・外部キーは、テーブル設計の中でも特に重要な「テーブル同士の関係性」を支える仕組みです。

主キー・外部キーとは？

主キー（PRIMARY KEY）とは

主キーとは、テーブルの中で1件のレコードを一意に識別するための列（または列の組み合わせ）です。

主キーには次のような制約が自動で付きます。

  • NULLを許可しない
  • 値の重複を許可しない

会員テーブルであれば、名前は同姓同名の人がいる可能性がありますが、idのような主キーであれば必ず1人を特定できます。

外部キー（FOREIGN KEY）とは

外部キーとは、あるテーブルの列が、別のテーブルの主キーを参照する仕組みです。

たとえば「注文」テーブルのmember_id列が「会員」テーブルのidを参照していれば、「その注文がどの会員のものか」を紐付けられます。

この仕組みによって、テーブル同士の関係性をデータベース自身が保証してくれるようになります。

なぜ主キー・外部キーが必要なのか

主キー・外部キーがないテーブル設計では、「本当は存在しない会員IDの注文データが登録できてしまう」といった、データの整合性が崩れる事態が起こり得ます。

主キー・外部キーは、こうしたデータの矛盾をデータベースレベルで防ぐための仕組みです。

基本の書き方

手順1：主キーを設定する

CREATE TABLE内でPRIMARY KEYを指定します。

CREATE TABLE members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

AUTO_INCREMENTを組み合わせると、新しいレコードを追加するたびに自動で連番の値が割り振られます。

一般的なアプリでは、意味を持たない連番のid列を主キーにする設計が多く使われます。

これをサロゲートキー（代理キー）と呼び、名前やメールアドレスのような意味のある値を主キーにするナチュラルキー（自然キー）と区別されます。

サロゲートキーは、名前変更などの業務都合でキーの値を変えたくなる心配がないというメリットがあります。

手順2：外部キーを設定する

外部キーはFOREIGN KEYとREFERENCESで設定します。

CREATE TABLE members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(id)
);

ordersテーブルのmember_idが、membersテーブルのidを参照する外部キーになりました。

この状態で、存在しないmember_id（たとえば999）を持つ注文を登録しようとすると、エラーになります。

INSERT INTO orders (member_id, total_price) VALUES (999, 5000);
-- Error: Cannot add or update a child row: a foreign key constraint fails

私が実際に開発したシステムでも、この外部キー制約のおかげで「削除した会員のIDがそのまま注文データに残ってしまう」というバグを未然に防げたことがあります。

手順3：削除・更新時の連動動作を指定する

外部キーには、参照元のデータが削除・更新されたときの挙動を指定できます。

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (member_id) REFERENCES members(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
オプション動作
CASCADE参照元が削除・更新されたら、こちらも連動して削除・更新する
SET NULL参照元が削除されたら、こちらの列をNULLにする
RESTRICT（デフォルト）参照されているデータの削除・更新を拒否する

「会員を削除したら、その会員の注文履歴も一緒に消したい」場合はCASCADE、「注文履歴は残しつつ会員情報だけ空にしたい」場合はSET NULLを使うといったように、業務要件に合わせて選択します。

つまずきやすいポイント：外部キー制約でINSERTがエラーになる

参照先が存在しないデータを登録しようとする

外部キーを設定すると、それまで通っていたINSERT文が急にエラーになることがあります。

❌ Before：会員テーブルにないIDで注文を登録しようとする

INSERT INTO orders (member_id, total_price) VALUES (50, 3000);
-- membersテーブルにid=50の会員が存在しない場合エラー

私が開発初期にテストデータを流し込んでいたとき、まさにこのエラーで作業が止まったことがあります。

原因は、注文データを先に投入し、会員データを後から投入する順番になっていたことでした。

✅ After：参照元のデータを先に登録してから、参照する側を登録する

-- 先にmembersにデータを入れる
INSERT INTO members (id, name) VALUES (50, '山田太郎');

-- その後でordersにデータを入れる
INSERT INTO orders (member_id, total_price) VALUES (50, 3000);

外部キーを持つテーブル同士でテストデータを投入する際は、「参照される側（親）→参照する側（子）」の順番を必ず意識しましょう。

応用：複合主キーとインデックスの関係

複数の列を組み合わせて主キーにする

「ある1つの列だけでは一意にならないが、複数の列の組み合わせなら一意になる」というケースでは、複合主キーを使います。

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_at DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

これは「学生が同じ講座に重複登録できない」という業務ルールを、そのままテーブル設計に落とし込んだ例です。

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

MySQL（InnoDB）では、外部キーを設定した列には自動的にインデックスが作成されます。

インデックスの詳しい仕組みは次のカテゴリで解説しますが、「外部キーはデータの整合性を守るだけでなく、検索性能にも関わる」ということをここで押さえておいてください。

まとめ

この記事のポイント

  • 主キーはレコードを一意に識別する列で、NULL・重複を許さない
  • 外部キーは別テーブルの主キーを参照し、テーブル同士の整合性を保証する
  • AUTO_INCREMENTのサロゲートキーを主キーにする設計が一般的
  • テストデータ投入時は「親テーブル→子テーブル」の順番を守る
  • ON DELETE CASCADEなどで削除・更新時の連動動作を制御できる

次に読むべき記事

主キー・外部キーで整合性を保つ考え方をさらに一歩進めると、テーブル設計の理論である「正規化」にたどり着きます。

次回は正規化の考え方を、第1〜第3正規形に沿って解説します。

→ 次の記事：正規化の考え方（第1〜第3正規形）

タグ: SQL, 初心者向け, DDL

タイトルとURLをコピーしました