はじめに
前回の記事「【初心者向け】Excel管理から学ぶデータベースの基礎」では、データベースの基本概念について解説しました。
今回は、リレーショナルデータベースの基本とも言える**主キー(Primary Key)と外部キー(Foreign Key)**について、初心者の方にもわかりやすく解説します。
この2つの概念を理解することで、データベースが「リレーショナル（関係）」と呼ばれる理由、そして複数のテーブルを関連付ける仕組みが理解できるようになります。
主キー(Primary Key)とは
主キーの定義
主キーは、テーブル内の各レコード（行）を一意に識別するための列（または列の組み合わせ）です。データベース設計において非常に重要な概念です。
身近な例
私たちの生活でも、一意に識別する仕組みはたくさんあります。
例: 社員管理
| 社員番号 | 名前 | 部署 |
|---|---|---|
| E001 | 田中太郎 | 営業部 |
| E002 | 佐藤花子 | 開発部 |
| E003 | 田中一郎 | 営業部 |
この表では、社員番号が主キーです。
- 同じ名前の「田中」さんが2人いても、社員番号で区別できる
- 社員番号は絶対に重複しない
- 社員番号があれば、その人を確実に特定できる
主キーの特徴
主キーには以下の3つの重要な制約があります。
- 一意性（ユニーク）: 主キーの値は、テーブル内で重複してはいけません
- NOT NULL: 主キーの値は、必ず値を持たなければなりません（NULLは許されません）
- 不変性: 一度設定した主キーの値は、原則として変更すべきではありません
1. 一意性（UNIQUE）
主キーの値は、テーブル内で重複してはいけません
❌ ダメな例
| user_id | name |
|---|---|
| 1 | 田中太郎 |
| 1 | 佐藤花子 |
user_idが重複している
2. NOT NULL（空値禁止）
主キーの値は、必ず値を持たなければなりません
❌ ダメな例
| user_id | name |
|---|---|
| 1 | 田中太郎 |
| NULL | 佐藤花子 |
NULL(登録なし)は許されない
3. 不変性
一度設定した主キーの値は、原則として変更すべきではありません。
主キーの変更は技術的には可能ですが、データの整合性を保つため推奨されません。
主キーの設定方法
パターン1: 連番（AUTO_INCREMENT）
サロゲートキー（代理キー） と呼ばれます。
サロゲートキーは、ビジネス上の意味を持たない、システムが自動生成する識別子です。
最も一般的な方法で、データベースが自動的に連番を割り振ります。
メリット:
- 重複の心配がない
- 業務ルールの変更に影響されない
- シンプルで管理しやすい
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY, -- 自動採番
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE
);
-- データ追加時はuser_idを指定しなくてOK
INSERT INTO users (name, email)
VALUES ('田中太郎', 'tanaka@example.com');
-- user_id = 1 が自動で割り振られる
INSERT INTO users (name, email)
VALUES ('佐藤花子', 'sato@example.com');
-- user_id = 2 が自動で割り振られる
パターン2: 業務コード
ナチュラルキー（自然キー） と呼ばれます。
ナチュラルキーは、ビジネス上の意味を持つ、実際の業務で使われる識別子です。
例えば、社員番号、商品コード、受注番号などが該当します。
メリット:
- 業務上の意味が明確
- 人が見て理解しやすい
- 既存の業務フローに合わせやすい
デメリット:
- 業務ルールの変更で値が変わる可能性がある
- 重複チェックが必要
CREATE TABLE customers (
customer_id VARCHAR(10) PRIMARY KEY, -- 顧客コード
name VARCHAR(100) NOT NULL,
phone VARCHAR(20)
);
-- 顧客コードを明示的に指定
INSERT INTO customers (customer_id, name, phone)
VALUES ('C001', '山田商店', '03-1234-5678');
どちらを使うべき？
-
システム内部のID → サロゲートキー（パターン1）
- ユーザーテーブル、注文テーブルなど
-
業務で使う識別子 → ナチュラルキー（パターン2）
- 商品コード、部品番号など、業務で既に使われているコード
実務ではサロゲートキーを主キーにして、ナチュラルキーをUNIQUE制約付きの別カラムにするパターンも多く使われます。
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY, -- サロゲートキー
product_code VARCHAR(20) UNIQUE NOT NULL, -- ナチュラルキー（業務コード）
name VARCHAR(100) NOT NULL,
price INT
);
複合主キー
複数のカラムを組み合わせて主キーにすることもできます。
例: 受注明細テーブル
CREATE TABLE order_details (
order_id INT, -- 注文ID
product_id INT, -- 商品ID
quantity INT, -- 数量
PRIMARY KEY (order_id, product_id) -- 複合主キー
);
| order_id | product_id | quantity |
|---|---|---|
| 1 | 101 | 2 |
| 1 | 102 | 1 |
| 2 | 101 | 3 |
この例では、同じ注文IDや同じ商品IDは複数回出現できますが、(注文ID, 商品ID)の組み合わせは一意でなければなりません。
注意点:
同一注文IDで同一商品を追加したい場合は、新しいレコードを作成するのではなく、
既存レコードの数量（quantity）を更新します。
使いどころ:
- 注文1に商品101が2個、商品102が1個
- 「注文ID + 商品ID」の組み合わせで一意に識別
外部キー(Foreign Key)とは
外部キーの定義
外部キーとは、別のテーブルの主キーを参照するカラムです。
これにより、テーブル同士を関連付けることができます。
前回の例を振り返る
usersテーブル（親テーブル）
| user_id | name | |
|---|---|---|
| 1 | 田中太郎 | tanaka@example.com |
| 2 | 佐藤花子 | sato@example.com |
ordersテーブル（子テーブル）
| order_id | user_id | product_name | price |
|---|---|---|---|
| 1 | 1 | ノートPC | 120000 |
| 2 | 1 | マウス | 2000 |
| 3 | 2 | キーボード | 15000 |
このordersテーブルのuser_idが外部キーです。
- usersテーブルのuser_idを参照している
- 「誰の注文か」を示している
外部キーの役割
外部キーには2つの重要な役割があります。
1. データの整合性を保証
参照整合性制約により、存在しないデータを参照できません。
-- usersテーブルにuser_id=999は存在しない
-- ❌ このINSERTは失敗する（外部キー制約違反）
INSERT INTO orders (user_id, product_name, price)
VALUES (999, '商品A', 1000);
-- エラー: 外部キー制約違反
-- user_id=999はusersテーブルに存在しません
2. カスケード操作
親テーブルの変更を子テーブルに自動反映できます。
ON DELETE CASCADE（削除の連鎖）
-- user_id=1のユーザーを削除すると...
DELETE FROM users WHERE user_id = 1;
-- そのユーザーの注文も自動で削除される
-- (外部キーにON DELETE CASCADEを設定している場合)
ON UPDATE CASCADE（更新の連鎖）
-- user_id=1を100に変更すると...
UPDATE users SET user_id = 100 WHERE user_id = 1;
-- ordersテーブルのuser_id=1も自動的に100に更新される
-- (外部キーにON UPDATE CASCADEを設定している場合)
外部キーの設定方法
基本的な設定
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_name VARCHAR(100),
price INT,
FOREIGN KEY (user_id) REFERENCES users(user_id)
-- ↑ この行が外部キー制約
);
カスケードオプション付き
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_name VARCHAR(100),
price INT,
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE -- 親が削除されたら子も削除
ON UPDATE CASCADE -- 親が更新されたら子も更新
);
カスケードオプションの種類
| オプション | 動作 | 具体例 | いつ使う？ |
|---|---|---|---|
| CASCADE | 親を削除/更新すると子も自動で削除/更新される | ユーザーを削除すると、そのユーザーの注文履歴も全て削除 | 親子が一体で、 親なしでは意味がないデータ |
|
RESTRICT (デフォルト) |
子が存在する場合、親を削除/更新できない | 注文がある顧客は削除不可 （先に注文を削除する必要がある） |
最も安全 誤ってデータを消さないため |
| SET NULL | 親を削除すると、子の外部キーがNULLになる | 担当者を削除しても、プロジェクトは残る（担当者なし状態） | 親は削除したいが、 子のデータは残したい時 |
| NO ACTION | RESTRICTとほぼ同じ （エラーになる） |
RESTRICTと同じ | 通常はRESTRICTを使う |
初心者へのアドバイス:
- 迷ったらRESTRICTを選びましょう（デフォルト設定）
- CASCADEは便利ですが、意図しない大量削除に注意
- SET NULLは外部キーに
NOT NULL制約があると使えません
リレーションシップの種類
テーブル間の関係には、主に3つのパターンがあります。
1対多（One to Many）
最も一般的なリレーションです。
例: ユーザーと注文
1人のユーザー → 複数の注文
1つの注文 → 1人のユーザー
usersテーブル（1）
| user_id | name |
|---|---|
| 1 | 田中太郎 |
| 2 | 佐藤花子 |
ordersテーブル（多）
| order_id | user_id | product |
|---|---|---|
| 1 | 1 | 商品A |
| 2 | 1 | 商品B |
| 3 | 2 | 商品C |
多対多（Many to Many）
中間テーブルを使って実現します。
例: 学生と授業
1人の学生 → 複数の授業を受講
1つの授業 → 複数の学生が受講
studentsテーブル
| student_id | name |
|---|---|
| 1 | 田中太郎 |
| 2 | 佐藤花子 |
coursesテーブル
| course_id | course_name |
|---|---|
| 101 | データベース |
| 102 | Web開発 |
enrollments（中間テーブル）
| student_id | course_id | grade |
|---|---|---|
| 1 | 101 | A |
| 1 | 102 | B |
| 2 | 101 | A |
CREATE TABLE enrollments (
student_id INT,
course_id INT,
grade VARCHAR(2),
PRIMARY KEY (student_id, course_id), -- 複合主キー
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
1対1（One to One）
あまり使われませんが、特定の用途で有用です。
例: ユーザーと詳細プロフィール
1人のユーザー → 1つの詳細プロフィール
usersテーブル
| user_id | username | |
|---|---|---|
| 1 | tanaka | tanaka@example.com |
user_profilesテーブル
| user_id | birth_date | address | hobby |
|---|---|---|---|
| 1 | 1990-01-01 | 東京都... | 読書 |
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY, -- 主キー兼外部キー
birth_date DATE,
address TEXT,
hobby VARCHAR(100),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
使いどころ:
- 頻繁にアクセスするデータと、あまり使わないデータを分ける
- セキュリティ上、分離したい情報がある場合
よくある間違いと注意点
❌ 間違い1: 外部キーなしでの関連付け
-- 外部キー制約を設定していない
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT -- ただのINT型、制約なし
);
-- 存在しないuser_idを挿入できてしまう
INSERT INTO orders (order_id, user_id)
VALUES (1, 999); -- user_id=999は存在しないのに成功！
問題点:
- データの整合性が保証されない
- 存在しないユーザーの注文が作れてしまう
- JOINしてもデータが取得できない矛盾が発生
解決策:
-- 外部キー制約を必ず設定する
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
❌ 間違い2: 複合主キーの誤った指定
-- ❌ 間違い: 複数のカラムに別々にPRIMARY KEYを指定
CREATE TABLE enrollments (
student_id INT PRIMARY KEY,
course_id INT PRIMARY KEY -- エラー！1つのテーブルに複数のPRIMARY KEY定義
);
問題点:
- 1つのテーブルには1つの主キーしか設定できない
- SQLエラーになる
正しい方法:
-- ⭕ 正しい: 複合主キーは括弧でまとめて指定
CREATE TABLE enrollments (
student_id INT,
course_id INT,
grade VARCHAR(2),
PRIMARY KEY (student_id, course_id) -- これで複合主キー
);
まとめ
この記事では、主キーと外部キーについて解説しました。
主キー(Primary Key)
- テーブル内のレコードを一意に識別する
- 一意性、NOT NULL、不変性の3つの制約
- サロゲートキー：システムが自動生成する識別子（AUTO_INCREMENT）
- ナチュラルキー：業務で使われる識別子（商品コード、社員番号など）
外部キー(Foreign Key)
- 別のテーブルの主キーを参照する
- データの整合性を保証する（参照整合性制約）
- カスケード操作で連動した変更が可能
リレーションシップ
- 1対多：最も一般的
- 多対多：中間テーブルを使用
- 1対1：データ分離が目的
主キーと外部キーを正しく設計することで、データの整合性が保たれ、安全で保守しやすいデータベースを構築できます。
次の記事
次回は正規化と非正規化について解説します。
データの重複を排除し、効率的なデータベースを設計するための原則を学びます。
- 正規化 - データの重複を排除する設計原則
- 非正規化 - パフォーマンス最適化のための例外的手法
- 正規化の実践例 - 具体的な適用方法