🔑 STEP 5: 主キーと外部キー

テーブル間の関係を作る「キー」の設計を学ぼう

📋 このステップで学ぶこと

主キーの役割と設計戦略
サロゲートキー vs ナチュラルキー
UUID vs 連番ID（AUTO_INCREMENT）
外部キーと参照整合性
複合主キーの使い所

学習時間の目安： 2時間　｜　前提知識： STEP 1-4修了

🎯 1. 主キー（Primary Key）とは？

STEP 3・4で学んだ「PK」の詳細

STEP 3のER図やSTEP 4のテーブル設計で、「PK: user_id」のように主キー（Primary Key）を書いてきました。

【STEP 3で書いたER図の例】
┌─────────────────┐
│  ユーザー (User)      │
├─────────────────┤
│ PK: user_id           │  ← これが主キー
│     username          │
│     email             │
└─────────────────┘

【STEP 4で書いたDDLの例】
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,  ← これが主キー
    username VARCHAR(50),
    email VARCHAR(255)
);

このSTEP 5では、主キーについて「なぜ必要なのか」「どう設計すべきか」を詳しく学びます。

主キーの定義

📝 主キーとは

主キー（Primary Key, PK）＝テーブル内の各レコードを一意に識別するためのカラム

主キーは「学籍番号」や「社員番号」のようなものです。
クラスには同じ名前の人がいるかもしれませんが、学籍番号は絶対に重複しません。
学籍番号があれば、「この人」を確実に特定できます。

主キーの3つの条件

主キーには、以下の3つの条件が必須です。

🔑 主キーの必須条件

1. 一意性（Uniqueness）

値が重複してはいけない。user_id が 1 のレコードは1つだけ。

2. NOT NULL（非NULL制約）

値が空（NULL）であってはいけない。すべてのユーザーは必ず user_id を持つ。

3. 不変性（Immutability）

値が変わらない（基本的に変更しない）。一度設定した user_id は変更しない。

主キーの4つの役割

🔍 レコードの一意性保証

テーブル内の各レコードを確実に識別できる。

🔗 他テーブルとの関連付け

外部キーで参照されるときの基準になる。

⚡ 高速な検索

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

✅ データの整合性

重複したレコードを防ぐことでデータ品質を保つ。

主キーの設定方法

— 方法1: カラム定義時に指定
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

— 方法2: 制約として指定
CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (user_id)
);

— 方法3: 複合主キー（複数カラムの組み合わせ）
CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

🆔 2. サロゲートキー vs ナチュラルキー

主キーには、サロゲートキーとナチュラルキーの2種類があります。どちらを使うかは重要な設計判断です。

サロゲートキー（代理キー）

📊 サロゲートキー（Surrogate Key）

定義：ビジネス的な意味を持たない、システムが自動生成する一意なID

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,  — サロゲートキー
    username VARCHAR(50),
    email VARCHAR(255)
);

— user_id は単なる連番で、ビジネス的な意味はない
— でも、各ユーザーを一意に識別できる

✅ サロゲートキーの特徴

システムが自動生成（AUTO_INCREMENT や UUID）
一意性が保証される
変更されない（不変）
ビジネスロジックから独立
外部キーとして使いやすい

ナチュラルキー（自然キー）

📊 ナチュラルキー（Natural Key）

定義：ビジネス的な意味を持つ、実際のデータをそのまま主キーにする

CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY,  — ナチュラルキー
    username VARCHAR(50)
);

— email がそのまま主キー
— ビジネス的な意味がある（メールアドレスはユニーク）

⚠️ ナチュラルキーの問題点

❌ 変更される可能性がある（メールアドレス変更など）
❌ 一意性が保証されない場合がある
❌ 外部キーとして参照しにくい（長い文字列）
❌ パフォーマンスが悪い（文字列インデックス）

どちらを使うべき？

💡 推奨：サロゲートキーを使う

現代のデータベース設計では、サロゲートキーが主流です。

理由：

不変性：ビジネスデータは変わる可能性があるが、IDは変わらない
シンプル：整数1つで済む（長い文字列を使わない）
パフォーマンス：整数のインデックスは高速
柔軟性：ビジネスルールが変わっても影響しない

— ✅ 良い設計（サロゲートキー）
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,  — サロゲートキー
    email VARCHAR(255) UNIQUE NOT NULL       — ユニーク制約で一意性を保証
);

— ❌ 避けるべき設計（ナチュラルキー）
CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY  — メールアドレスが主キー
);
— 問題：メールアドレスが変わったら、外部キーも全て更新する必要

📝 例外：ナチュラルキーを使う場合

国コードテーブル（’JP’, ‘US’ など、絶対に変わらない）
通貨コードテーブル（’JPY’, ‘USD’ など）
マスタデータで、データが固定的な場合

🔢 3. UUID vs 連番ID（AUTO_INCREMENT）

サロゲートキーを生成する方法として、連番ID（AUTO_INCREMENT）とUUIDがあります。

連番ID（AUTO_INCREMENT）

📊 AUTO_INCREMENT

定義：1, 2, 3, … と自動的に連番を振る方式

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

INSERT INTO users (username) VALUES (‘山田太郎’);  — user_id = 1
INSERT INTO users (username) VALUES (‘佐藤花子’);  — user_id = 2
INSERT INTO users (username) VALUES (‘鈴木一郎’);  — user_id = 3

📊 AUTO_INCREMENT の特徴

観点	メリット	デメリット
サイズ	4バイト（INT）で済む	–
可読性	1, 2, 3 と読みやすい	–
パフォーマンス	整数インデックスは高速	–
セキュリティ	–	IDから登録数がバレる
分散システム	–	複数DBで同じIDが発生

UUID（Universally Unique Identifier）

📊 UUID

定義：128ビットのランダムな一意識別子（例：550e8400-e29b-41d4-a716-446655440000）

CREATE TABLE users (
    user_id CHAR(36) PRIMARY KEY,  — UUID は36文字
    username VARCHAR(50)
);

INSERT INTO users (user_id, username) 
VALUES (UUID(), ‘山田太郎’);

📊 UUID の特徴

観点	メリット	デメリット
一意性	グローバルに一意	–
セキュリティ	IDから情報が推測不可	–
分散システム	どこで生成しても重複しない	–
サイズ	–	36バイト（INT の9倍）
可読性	–	人間には判別しづらい

どちらを使うべき？

💡 選択基準

AUTO_INCREMENT を使う場合（推奨）

✅ 単一データベースのシステム
✅ パフォーマンスを重視
✅ シンプルな設計が好み
✅ 小〜中規模のシステム

UUID を使う場合

✅ 分散システム（マイクロサービスなど）
✅ 複数DBをマージする可能性
✅ IDを外部に公開する（APIなど）
✅ セキュリティ重視（IDから情報を隠したい）

💡 結論

迷ったら AUTO_INCREMENT を使いましょう。
シンプルで、パフォーマンスも良く、ほとんどのケースで十分です。
UUID は特別な理由がある場合のみ使いましょう。

🔗 4. 外部キー（Foreign Key）と参照整合性

外部キーとは

📝 外部キーとは

外部キー（Foreign Key, FK）＝他のテーブルの主キーを参照するカラム

外部キーは「つながり」を表す番号です。
例えば、図書館の貸出記録で「会員番号」を書いておけば「誰が借りたか」がわかる。
この「会員番号」が外部キーです。

外部キーの設定

— 親テーブル（顧客）
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(255)
);

— 子テーブル（注文）
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,  — 外部キー
    order_date DATE,
    total_amount DECIMAL(10, 2),
    
    — 外部キー制約の定義
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

参照整合性とは

参照整合性（Referential Integrity）とは、外部キーで参照されるデータが、必ず親テーブルに存在することを保証する仕組みです。

✅ 参照整合性が保証すること

存在しない顧客の注文は登録できない
注文がある顧客は削除できない（デフォルト）
データの矛盾を防ぐ

外部キー制約のオプション

親レコードが削除/更新されたとき、子レコードをどうするか指定できます。

CASCADE（連鎖）

親を削除/更新したら、子も一緒に削除/更新

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

— 顧客を削除したら、その顧客の注文も全て削除される

SET NULL

親を削除したら、子の外部キーをNULLにする

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE SET NULL

— 顧客を削除したら、注文の customer_id が NULL になる
— （顧客は削除されたが、注文履歴は残す）

RESTRICT / NO ACTION（デフォルト）

子レコードがあれば、親の削除/更新を拒否

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT

— 注文がある顧客は削除できない（エラーになる）

どのオプションを使うべき？

💡 選択基準

CASCADE を使う場合

親と子が強く結びついている場合
例：ユーザー削除 → そのユーザーの投稿も全削除

SET NULL を使う場合

親が削除されても、子の履歴を残したい場合
例：社員削除 → でもその社員が作った記録は残す

RESTRICT を使う場合（推奨）

誤って重要なデータを削除したくない場合
例：顧客削除 → 注文があれば削除できない（安全）

🔢 5. 複合主キーの使い所

複合主キーとは

📝 複合主キーとは

複合主キー（Composite Primary Key）＝2つ以上のカラムを組み合わせて主キーにすること

CREATE TABLE order_details (
    order_id INT,       — 複合主キーの一部
    product_id INT,     — 複合主キーの一部
    quantity INT,
    unit_price DECIMAL(10, 2),
    
    PRIMARY KEY (order_id, product_id),  — 複合主キー
    
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

— (order_id, product_id) の組み合わせで一意
— 例：注文1の商品A、注文1の商品B、注文2の商品A など

複合主キーを使う場面

1. 多対多の中間テーブル

学生と授業の履修テーブル

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrolled_at TIMESTAMP,
    PRIMARY KEY (student_id, course_id)
);

2. 明細テーブル

注文と商品の注文詳細テーブル

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

3. 時系列データ

ユーザーと日付のログテーブル

CREATE TABLE user_daily_stats (
    user_id INT,
    date DATE,
    login_count INT,
    PRIMARY KEY (user_id, date)
);

複合主キー vs サロゲートキー

🤔 どちらがいい？

方式	メリット	デメリット
複合主キー	追加のIDカラム不要	外部キーで参照しにくい
サロゲートキー	シンプル、参照しやすい	カラムが1つ増える

💡 推奨パターン

基本的にはサロゲートキー（ID追加）がおすすめです。

理由：シンプルで理解しやすく、外部キーで参照しやすい。
ただし、シンプルな中間テーブルでは複合主キーでも問題ありません。

📝 STEP 5 のまとめ

✅ このステップで学んだこと

主キーは、レコードを一意に識別するための必須要素
主キーの条件：一意性、NOT NULL、不変性
サロゲートキー（システム生成ID）が推奨される
IDの生成方法：AUTO_INCREMENTが一般的、分散システムではUUID
外部キーは、他テーブルの主キーを参照し、参照整合性を保証
外部キー制約：CASCADE、SET NULL、RESTRICTなどのオプション
複合主キーは、複数カラムの組み合わせで一意性を保証

💡 設計の基本原則

✅ すべてのテーブルに主キーを設定する
✅ 基本的にサロゲートキー（INT AUTO_INCREMENT）を使う
✅ テーブル間の関係には外部キー制約を設定する
✅ 外部キーオプションは、ビジネスロジックに合わせて選ぶ

🎯 次のステップへ

次のSTEP 6では、命名規則とベストプラクティスを学び、読みやすく保守しやすいデータベース設計を身につけます！

📝 練習問題

問題 1 基礎

主キーの3つの必須条件を答えてください。

【解答】

一意性（Uniqueness）：値が重複してはいけない
NOT NULL：値が空（NULL）であってはいけない
不変性（Immutability）：値が変わらない（基本的に変更しない）

問題 2 基礎

サロゲートキーとナチュラルキーの違いを説明してください。

【解答】

サロゲートキー：システムが自動生成する一意なID。ビジネス的な意味を持たない。
例：user_id INT AUTO_INCREMENT

ナチュラルキー：ビジネス的な意味を持つデータをそのまま主キーにする。
例：email VARCHAR(255) PRIMARY KEY

問題 3 基礎

外部キー制約の役割を説明してください。

【解答】

外部キー制約は、参照整合性を保証する役割を持ちます。
・存在しない親レコードを参照する子レコードを登録できなくする
・子レコードがある親レコードを削除できなくする（または連鎖削除）
・データの矛盾を防ぎ、データの整合性を保つ

問題 4 応用

ON DELETE CASCADE と ON DELETE RESTRICT の違いを説明してください。

【解答】

ON DELETE CASCADE：親レコードを削除したら、子レコードも一緒に削除される。
例：顧客を削除 → その顧客の注文も全て削除

ON DELETE RESTRICT：子レコードがある場合、親レコードの削除を拒否する。
例：注文がある顧客は削除できない（エラーになる）

問題 5 応用

以下のテーブル定義に外部キー制約を追加してください。

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE
);

【解答】

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,
    
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

問題 6 応用

複合主キーを使うべき場面を2つ挙げてください。

【解答例】

多対多の中間テーブル：学生と授業の履修テーブル（student_id, course_id）
明細テーブル：注文詳細テーブル（order_id, product_id）

他にも、時系列データ（user_id, date）などがあります。

問題 7 発展

なぜメールアドレスを主キーにするのは推奨されないのですか？

【解答】

変更される可能性：メールアドレス変更時、外部キーも全て更新が必要
パフォーマンス：文字列のインデックスは整数より遅い
サイズ：VARCHAR(255)はINT(4バイト)より大きい
複雑性：外部キーで長い文字列を参照することになり複雑

推奨：サロゲートキー（user_id INT）を主キーにし、メールアドレスにはUNIQUE制約をつける。

問題 8 発展

以下の要件を満たすテーブルを設計してください。

【要件】学生と授業の履修管理
・学生は複数の授業を履修できる
・授業には複数の学生が履修する
・履修日時と成績（0〜100点）を記録する

【解答例】

— 学生テーブル
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

— 授業テーブル
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(200) NOT NULL
);

— 履修テーブル（中間テーブル）
CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade TINYINT UNSIGNED,
    
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE KEY (student_id, course_id)
);

❓ よくある質問

Q1: すべてのテーブルに主キーは必要ですか？

はい、必須です。主キーがないテーブルは、レコードを一意に識別できず、重複したレコードが入る可能性があり、パフォーマンスも悪くなります。

Q2: 外部キー制約は必ず設定すべきですか？

基本的には設定すべきです。外部キー制約を設定することで、データの整合性が保証されます。例外として、大量データでパフォーマンスを最優先する場合は省略することもありますが、慎重に判断しましょう。

Q3: AUTO_INCREMENT と UUID、どちらを使うべきですか？

迷ったら AUTO_INCREMENT を使いましょう。シンプルで、パフォーマンスも良く、ほとんどのケースで十分です。UUID は分散システムやセキュリティ重視の場合のみ使いましょう。

Q4: 複合主キーとサロゲートキー、どちらを使うべきですか？

基本的にはサロゲートキー（ID追加）がおすすめです。シンプルで理解しやすく、外部キーで参照しやすいです。ただし、シンプルな中間テーブルでは複合主キーでも問題ありません。

Q5: AUTO_INCREMENT の値は途中から変更できますか？

はい、可能です。ALTER TABLE users AUTO_INCREMENT = 1000; で、次のIDを1000から始められます。ただし、既存のIDとの重複には注意が必要です。

📝

学習メモ

データベース設計・データモデリング - Step 5

📋 過去のメモ一覧 ▼

🔑 STEP 5: 主キーと外部キー

🎯 1. 主キー（Primary Key）とは？

STEP 3・4で学んだ「PK」の詳細

主キーの定義

主キーの3つの条件

主キーの4つの役割

主キーの設定方法

🆔 2. サロゲートキー vs ナチュラルキー

サロゲートキー（代理キー）

ナチュラルキー（自然キー）

どちらを使うべき？

🔢 3. UUID vs 連番ID（AUTO_INCREMENT）

連番ID（AUTO_INCREMENT）

UUID（Universally Unique Identifier）

どちらを使うべき？

🔗 4. 外部キー（Foreign Key）と参照整合性

外部キーとは

外部キーの設定

参照整合性とは

外部キー制約のオプション

どのオプションを使うべき？

🔢 5. 複合主キーの使い所

複合主キーとは

複合主キーを使う場面

複合主キー vs サロゲートキー

📝 STEP 5 のまとめ

📝 練習問題

主キーの3つの必須条件を答えてください。

サロゲートキーとナチュラルキーの違いを説明してください。

外部キー制約の役割を説明してください。

ON DELETE CASCADE と ON DELETE RESTRICT の違いを説明してください。

以下のテーブル定義に外部キー制約を追加してください。

複合主キーを使うべき場面を2つ挙げてください。

なぜメールアドレスを主キーにするのは推奨されないのですか？

以下の要件を満たすテーブルを設計してください。【要件】学生と授業の履修管理・学生は複数の授業を履修できる・授業には複数の学生が履修する・履修日時と成績（0〜100点）を記録する

❓ よくある質問

Q1: すべてのテーブルに主キーは必要ですか？

Q2: 外部キー制約は必ず設定すべきですか？

Q3: AUTO_INCREMENT と UUID、どちらを使うべきですか？

Q4: 複合主キーとサロゲートキー、どちらを使うべきですか？

Q5: AUTO_INCREMENT の値は途中から変更できますか？

学習メモ

以下の要件を満たすテーブルを設計してください。

【要件】学生と授業の履修管理
・学生は複数の授業を履修できる
・授業には複数の学生が履修する
・履修日時と成績（0〜100点）を記録する