第10章データベースのキー概念 — 主キー・外部キー・複合キー・代理キー

この章で学ぶこと

主キー / 外部キー / 候補キー / 複合キーの違いを自分の言葉で説明できる
代理キー (surrogate key) と自然キー (natural key) のトレードオフを理解し、状況に応じて選べる
外部キー制約の ON DELETE / ON UPDATE の挙動 (CASCADE / RESTRICT / SET NULL) を区別できる

前提知識

キーの全体像

リレーショナルデータベースには、目的が異なる複数の「キー」があります。

用語	役割
候補キー	レコードを一意に識別できる属性 (の組み合わせ)
主キー	候補キーの中から選ばれた「正式な識別子」
代替キー	主キーに選ばれなかった他の候補キー (UNIQUE 制約で守る)
外部キー	別テーブルの主キーを参照する属性
複合キー	複数のカラムをまとめて構成するキー
代理キー	システムが自動生成する意味のない識別子 (例: 連番 ID)
自然キー	業務上の意味を持つ属性そのものをキーにしたもの (例: ISBN・税務 ID)

候補キー (Candidate Key) — 一意性のある属性

候補キーは、レコードを一意に識別できる属性 (またはその組み合わせ) です。条件は 2 つ:

一意性: 同じテーブル内で値が重複しない
最小性: 必要最小限の属性で構成されている (余計な属性を含まない)

例: 社員テーブルでの候補キー探し

次のような社員テーブルを考えます。

社員番号	メール	社会保険番号	氏名	電話
1001	yamada@example.com	123-45-6789	山田太郎	090-...
1002	suzuki@example.com	234-56-7890	鈴木花子	080-...

候補キーになり得るカラムを検討します。

候補	一意性	候補キーか
社員番号	会社が一意に振る	◯ 候補キー
メール	個人ごとに違う	◯ 候補キー
社会保険番号	国が一意に振る	◯ 候補キー
氏名	同姓同名がいる	✕ 候補キーでない
電話	変更される可能性 / 共有がある	✕ 候補キーでない

このように、1 つのテーブルに複数の候補キーが存在することがあります。その中から「主キー」を選びます。

主キー (Primary Key) — テーブルの「正式な識別子」

主キーは、候補キーの中から選ばれた「このテーブルではこれで一意」と決めた識別子です。

主キーの良い例と悪い例

主キーの選び方
-- 良い例: 自動採番の代理キーを主キー、業務コードは UNIQUE で守る
CREATE TABLE products (
    id            SERIAL PRIMARY KEY,                       -- 代理キー
    code          VARCHAR(20) UNIQUE NOT NULL,              -- 業務キー (代替キー)
    name          VARCHAR(200) NOT NULL,
    price         NUMERIC(10, 2) NOT NULL
);

-- 悪い例: メールアドレスを主キー
CREATE TABLE customers_bad (
    email         VARCHAR(255) PRIMARY KEY,                 -- メールは変更される
    name          VARCHAR(100),
    phone         VARCHAR(20)
);

メールアドレスを主キーにすると、ユーザーがメール変更したときに customers.email を書き換える必要が出ます。さらに、このメールを外部キーで参照しているテーブル (orders.customer_email など) のすべての行も書き換える必要があり、参照整合性の保守が大変になります。

「主キーは変わらないものを選ぶ」のが原則 です。

外部キー (Foreign Key) — 別テーブルへの参照

外部キーは、別のテーブルの主キーを指す値で、テーブル間の関係を表現します。

外部キーの定義 (PostgreSQL)
CREATE TABLE departments (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    location  VARCHAR(100)
);

CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id)
                  ON DELETE RESTRICT
                  ON UPDATE CASCADE,
    hire_date     DATE NOT NULL
);

外部キーの効果

INSERT INTO departments (id, name, location) VALUES (10, '営業部', '東京');
INSERT INTO departments (id, name, location) VALUES (20, '開発部', '大阪');

-- OK: 存在する department_id を参照
INSERT INTO employees (name, department_id, hire_date)
VALUES ('山田 太郎', 10, '2026-01-01');

-- NG: 存在しない department_id (99) を参照
INSERT INTO employees (name, department_id, hire_date)
VALUES ('鈴木 花子', 99, '2026-01-01');  -- ERROR: 外部キー制約違反

これにより、「存在しない部署 ID を持つ社員」のような矛盾したデータが入るのを防げます (参照整合性)。

ON DELETE / ON UPDATE の挙動

参照先 (departments) の行が削除 / 更新されたときの、参照側 (employees) の挙動を指定できます。

アクション	挙動
`NO ACTION` (デフォルト)	参照されている行の削除 / 更新はエラー。何も指定しないとこれになる (PostgreSQL: CREATE TABLE)
`RESTRICT`	NO ACTION とほぼ同等 (違いはチェックのタイミング: NO ACTION はトランザクション末尾まで遅延できるが RESTRICT は即時)
`CASCADE`	参照先の変更を参照側にも自動波及 (削除なら employees も削除、更新なら employees の `department_id` も更新)
`SET NULL`	参照側のカラムを NULL にする (外部キー列は NULL 許可が前提)

ON DELETE CASCADE の使い所

CASCADE は便利ですが、「親が消えたら子も消える」という連鎖が広範囲に及ぶと、誤って大量データを消す事故になります。CASCADE を設定するのは「明らかに子は親に従属する」関係 (注文と注文明細など) に限定するのが安全です。社員と部署のように「部署が消えても社員は別部署に異動」というケースでは RESTRICT を選び、削除前に手動で異動処理を行うほうが安全です。

複合キー (Composite Key) — 複数カラムの組み合わせ

複合キーは、1 つのカラムでは一意にならないが、複数カラムの組み合わせなら一意になる場合に使うキーです。多対多の関係を表す中間テーブルでよく使います。

履修テーブル (複合主キー)
CREATE TABLE enrollments (
    student_id        INTEGER NOT NULL REFERENCES students(id),
    course_id         INTEGER NOT NULL REFERENCES courses(id),
    enrollment_date   DATE NOT NULL,
    grade             CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
    (1, 101, '2026-04-01'),  -- 学生 1 が科目 101 を履修
    (1, 102, '2026-04-01'),  -- 学生 1 が科目 102 を履修
    (2, 101, '2026-04-01');  -- 学生 2 が科目 101 を履修

-- NG: 同じ (student_id, course_id) の組み合わせは重複できない
INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES (1, 101, '2026-09-01');  -- ERROR: 主キー制約違反

多対多の表現方法と中間テーブルの設計は第 14 章多対多で詳しく扱います。

代理キー vs 自然キー — 現代ベストプラクティス

代理キー (Surrogate Key) と自然キー (Natural Key) は、主キーの「選び方」の 2 つの流派です。

代理キー — システムが自動生成する識別子

代理キーを主キーにした例
CREATE TABLE customers_surrogate (
    id            SERIAL PRIMARY KEY,              -- 代理キー (連番)
    company_code  VARCHAR(20) UNIQUE NOT NULL,     -- 業務コード
    name          VARCHAR(200) NOT NULL,
    tax_id        VARCHAR(20) UNIQUE NOT NULL      -- 税務番号
);

代理キーは「SERIAL BIGSERIAL UUID」のような、業務上の意味を持たない値です。

自然キー — 業務上の意味を持つキー

自然キーを主キーにした例
CREATE TABLE customers_natural (
    tax_id        VARCHAR(20) PRIMARY KEY,         -- 税務番号を主キーに
    name          VARCHAR(200) NOT NULL,
    phone         VARCHAR(20)
);

トレードオフ表

観点	代理キー	自然キー
例	`SERIAL` / `UUID` / Snowflake ID	ISBN / 税務 ID / ISO 国コード / SKU
不変性	◯ システムが管理し変わらない	△ 業務都合で変わることがある (例: メール変更)
長さ	◯ 短い (4-8 バイト)	△ 長くなることがある (ISBN 13 文字 / UUID 36 文字)
意味	✕ 値を見ても何の行か分からない	◯ 値を見れば業務上の意味が分かる
JOIN 性能	◯ 整数比較で高速	△ 文字列比較で遅くなることがある
URL に出すリスク	△ 連番だと推測されるリスク (UUID なら ◯)	△ 業務情報を晒すリスク

現代ベストプラクティス: ハイブリッド

近代的な Web アプリ開発の標準的なやり方はハイブリッドです (Baeldung Natural vs Surrogate Keys / MSSQLTips Surrogate vs Natural Key 参照)。

主キー は代理キー (SERIAL / UUID) — 不変性・性能を取る
業務キー は UNIQUE 制約で守る — 業務上の重複も防げる

ハイブリッド設計 (推奨)
CREATE TABLE customers (
    id            SERIAL PRIMARY KEY,                  -- 代理キー (主キー)
    customer_code VARCHAR(20) UNIQUE NOT NULL,         -- 業務キー (代替キー)
    name          VARCHAR(200) NOT NULL,
    email         VARCHAR(255) UNIQUE NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

これにより、他テーブルからは customer_id (代理キー) で参照しつつ、業務的な一意性は customer_code や email の UNIQUE 制約で確保できます。

SERIAL と IDENTITY、INTEGER と BIGINT

本ガイドのコード例は簡潔さを優先して SERIAL (= INTEGER の連番) を使っていますが、実務では次の 2 点も検討してください。

自動採番の書き方: PostgreSQL 10 以降は SQL 標準準拠の IDENTITY 列 (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY) が使えます。公式ドキュメントは SERIAL を「真の型ではなく便宜的な書き方」と位置づけており、新規設計では IDENTITY 列が標準準拠の選択です。
型の大きさ: 主キーの default は BIGINT (BIGSERIAL) が安全です。INTEGER は約 21 億で枯渇します (第 3 章・設計テンプレート集参照)。
UUID を主キーにする場合: ランダムな UUIDv4 はインデックスの断片化を招きます。書き込みが多いテーブルでは UUIDv7 を検討してください (第 19 章参照)。

例外: 自然キーが向いている場面

ハイブリッドが原則ですが、自然キーが安定するドメインもあります。

ドメイン	例	理由
ISO 国コード	`JP`, `US`, `FR`	国際標準で不変
通貨コード	`JPY`, `USD`, `EUR`	ISO 4217 で不変
ISBN	`978-4-12345-678-9`	一度発行されたら変わらない (書籍識別の世界標準)
短く不変なルックアップテーブル	`status_codes` (10 行程度)	値そのものが安定 / 行数が少なく性能影響が小さい

このような「絶対に変わらない短い文字列」は、自然キーをそのまま主キーにしても問題ありません。

Jr 向けデフォルト戦略

迷ったら代理キーを主キーに + 業務キーを UNIQUE 制約のハイブリッドにしておきましょう。ISO コードのように「絶対に変わらない」と分かるものだけ自然キーを許す、という判断軸が安全です。

キー設計のチェックリスト

実践例: EC サイトのキー設計

EC サイトの主要テーブル (PostgreSQL)
-- カテゴリ (自己参照あり)
CREATE TABLE categories (
    id                  SERIAL PRIMARY KEY,
    name                VARCHAR(100) NOT NULL,
    parent_category_id  INTEGER REFERENCES categories(id),
    display_order       INTEGER NOT NULL DEFAULT 0
);

-- 商品 (カテゴリへの外部キー、業務コード SKU は UNIQUE)
CREATE TABLE products (
    id            BIGSERIAL PRIMARY KEY,
    sku           VARCHAR(50) UNIQUE NOT NULL,
    name          VARCHAR(200) NOT NULL,
    category_id   INTEGER NOT NULL REFERENCES categories(id),
    price         NUMERIC(10, 2) NOT NULL CHECK (price > 0)
);

-- 顧客 (メールは UNIQUE で守るが PK でない)
CREATE TABLE customers (
    id              BIGSERIAL PRIMARY KEY,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 注文 (顧客への外部キー、注文番号は表示用 UNIQUE)
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    order_number    VARCHAR(20) UNIQUE NOT NULL,
    customer_id     BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    order_date      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_amount    NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0)
);

-- 注文明細 (複合主キー = order_id + product_id)
CREATE TABLE order_items (
    order_id      BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id    BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

ポイント:

各テーブルの主キーは代理キー (SERIAL / BIGSERIAL)
業務キー (sku / email / order_number) は UNIQUE 制約
orders.customer_id は ON DELETE RESTRICT (顧客削除は明示的にハンドリング)
order_items.order_id は ON DELETE CASCADE (注文ごと消えるなら明細も一緒に消える)

演習

演習 1: 候補キーと主キーの選択

ある書籍テーブルが次のようなカラムを持つとします。

id (連番)
isbn (13 文字、国際的に一意)
title (書名、同名はあり得る)
publisher_id (出版社 ID)
published_at (出版日)

候補キーになり得るカラムを列挙し、主キーとして選ぶならどれが適切か、理由とともに答えてください。

解答例

候補キー:

id (連番なので一意)
isbn (国際標準で一意)
(場合によっては (title, publisher_id, published_at) の複合 — 同じ本を別出版社が出すことはあっても、同じ出版社が同じ書名を同じ日に出すケースは稀)

主キーとして選ぶなら:

id (代理キー) を主キー + isbn を UNIQUE 制約 のハイブリッドが推奨です。理由:

id は SERIAL で短く高速、外部キー参照も整数比較で効率的
isbn は ISBN という国際標準なので、一見「自然キーで十分」だが、ISBN を持たない書籍 (自費出版・社内資料・古書・電子書籍の一部) も扱う可能性があり、NOT NULL 制約をかけにくい (主キーは NOT NULL 必須)
主キーに isbn を選ぶと「ISBN なし書籍」を扱えない設計になる

CREATE TABLE books (
    id              SERIAL PRIMARY KEY,
    isbn            VARCHAR(13) UNIQUE,                   -- NULL 許容で OK
    title           VARCHAR(500) NOT NULL,
    publisher_id    INTEGER NOT NULL REFERENCES publishers(id),
    published_at    DATE
);

ありがちな間違い: ISBN を主キーに選ぶこと。一見「ISBN は国際標準で不変」と思えるが、扱う対象に「ISBN を持たない書籍」が混じる可能性を見落とすと、後で困ります。

演習 2: ON DELETE の選択

次の関係について、ON DELETE をどう設定するか (CASCADE / RESTRICT / SET NULL) とその理由を答えてください。

comments テーブルが posts テーブルを参照 (comments.post_id)
employees テーブルが departments テーブルを参照 (employees.department_id)
posts テーブルが users テーブルを参照 (posts.user_id、論理削除を併用したい)

解答例

ケース	設定	理由
1. comments → posts	`ON DELETE CASCADE`	投稿が削除されたらコメントも一緒に消えるのが自然
2. employees → departments	`ON DELETE RESTRICT`	部署が消えるからといって社員が消えるのはおかしい。明示的に異動処理してから部署を削除すべき
3. posts → users	`ON DELETE RESTRICT` or `SET NULL`	ユーザー削除時に投稿を一緒に消すか / 「削除されたユーザー」として残すかの業務判断による

3 についての補足: SNS で「アカウント削除しても投稿は残してほしい」という UX なら、user_id を NULL 許容にして ON DELETE SET NULL を設定する設計があります。逆に「アカウント削除と同時に投稿も全て消す」なら CASCADE。業務要件によって判断が変わるため、ヒアリングが必要です。

ありがちな間違い: 「とりあえず CASCADE で動くから」と全てに CASCADE をつけること。意図しないデータ消失事故の温床になります。迷ったら RESTRICT を明示 (安全側) し、必要な場面だけ CASCADE を許可するのが安全です。

まとめ

この章で学んだことを整理します。

候補キーは「一意性のある属性」、その中から選ばれた正式な識別子が主キー
主キーは「変わらない・短い・意味を持たない」ものを選ぶ
外部キーは別テーブルの主キーを参照し、参照整合性を保つ
ON DELETE は指定しないと NO ACTION。迷ったら RESTRICT を明示し、CASCADE は「親に明らかに従属する」関係に限定
複合キーは多対多の中間テーブルで使う
現代ベストプラクティスは ハイブリッド (代理キー = PK + 自然キー = UNIQUE 制約)

次章では、データの重複と矛盾を排除する設計手法 — 正規化を扱います。

次の章

第 11 章データベース正規化

この章で学ぶこと​

キーの全体像​

候補キー (Candidate Key) — 一意性のある属性​

例: 社員テーブルでの候補キー探し​

主キー (Primary Key) — テーブルの「正式な識別子」​

主キーの良い例と悪い例​

外部キー (Foreign Key) — 別テーブルへの参照​

外部キーの効果​

ON DELETE / ON UPDATE の挙動​

複合キー (Composite Key) — 複数カラムの組み合わせ​

代理キー vs 自然キー — 現代ベストプラクティス​

代理キー — システムが自動生成する識別子​

自然キー — 業務上の意味を持つキー​

トレードオフ表​

現代ベストプラクティス: ハイブリッド​

例外: 自然キーが向いている場面​

キー設計のチェックリスト​

実践例: EC サイトのキー設計​

演習​

まとめ​