【図解とSQLで理解】ER図と外部キーの基本｜リレーションと参照整合性制約をやさしく解説

データベースの設計、特に応用情報技術者試験の勉強を始めると、必ずと言っていいほど登場するのが「ER図」や「外部キー」といった専門用語です。しかし、「主キーと外部キーって、名前は似ているけど何が違うの？」「ER図に描かれている線や記号には、どんな意味があるの？」と、最初のステップでつまずいてしまう方も少なくありません。これらの概念は、データベースの骨格をなす非常に重要な部分ですが、それぞれの関係性を正しく理解しないまま暗記に頼ってしまうと、応用問題に対応できなくなってしまいます。

ご安心ください。この記事では、データベース設計の核心である「ER図」から始まり、「リレーションシップ」「外部キー」「参照整合性制約」という一連の流れを、豊富な図解と実際に動かせるSQLの具体例を交えながら、一つひとつ丁寧に解説していきます。この記事を読み終える頃には、点と点だった知識が線でつながり、データベースの構造が直感的に理解できるようになっているはずです。

ER図とは？データベースの設計図

データベースを構築する際、いきなりテーブルを作り始めることはありません。家を建てる前に設計図が必要なのと同じように、データベースにも「どのようなデータを」「どのように関連付けて」保存するのかを示す設計図が必要です。その設計図の役割を果たすのがER図（Entity Relationship Diagram）です。

ER図は、直訳すると「実体関連図」となり、その名の通りシステムで扱うデータ（エンティティ）と、そのデータ同士の（リレーションシップ）を可視化するための図です。この図を描くことで、データベースの全体像が明確になり、開発者や設計者の間で認識のズレが生まれるのを防ぎます。

ER図の基本要素を覚えよう

ER図は、主に3つの基本要素で構成されています。まずはこの3つを覚えましょう。

まずはイメージで理解：顧客と注文の関係

言葉だけでは分かりにくいので、具体的な例で見てみましょう。
例えば、オンラインストアのデータベースを考えます。そこには「顧客」の情報と「注文」の情報があります。

このとき、「1人の顧客が、複数の注文を行うことができる」という関係性が成り立ちます。これをER図のイメージで表すと、以下のようになります。

[ 顧客 ] ───< 注文する >─── [ 注文 ]

このように、ER図はデータ（顧客、注文）とその関係性（注文する）をシンプルに表現するためのツールです。次のセクションでは、この「関係性（リレーションシップ）」について、さらに詳しく掘り下げていきます。

リレーションシップとは？表と表をつなぐ線の意味

ER図でエンティティ（テーブル）を四角で描きましたが、それだけではただの箱の集まりです。ER図で最も重要なのは、エンティティ同士をつなぐ「線」、すなわちリレーションシップ（関連）です。この線が、「どのテーブルが、どのテーブルと、どのように関わり合うのか」というルールを定義します。

リレーションシップには、大きく分けて以下の3種類があります。応用情報技術者試験などでも頻出する、非常に重要な概念です。

• 一対一（1 to 1）: 1つのレコードが、相手のテーブルの1つのレコードにしか対応しない関係。（例：「社員」と「社員詳細情報」）
• 一対多（1 to N）: 1つのレコードが、相手のテーブルの複数のレコードに対応する関係。（例：「顧客」と「注文」）
• 多対多（N to N）: 互いに複数のレコードに対応しあう関係。（例：「学生」と「講義」）

この中で、データベース設計において最も基本かつ頻繁に登場するのが「一対多」の関係です。

一対多（1:N）の代表例

先ほどの例を思い出してみましょう。「1人の顧客が、複数の注文履歴を持つ」という構造は、まさに「一対多」の典型例です。

• 顧客テーブル: 1人の顧客の情報を持つ「親」の役割を果たします。これを親テーブルと呼びます。
• 注文テーブル: 複数の注文情報が格納される「子」の役割を果たします。これを子テーブルと呼びます。

ER図上の [顧客] ─── [注文] という1本の線は、この「親子の関係性」を示しているのです。

SQLで表すリレーション

では、このER図上の「線」は、実際のデータベースではどのように表現されるのでしょうか。それがSQLのCREATE TABLE文です。ER図で定義した関係性は、テーブルを作成する際の「制約」としてコードに落とし込まれます。

-- 親テーブル
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- 子テーブル
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_name VARCHAR(100),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

このSQL文の最後の行 FOREIGN KEY (customer_id) REFERENCES customers(customer_id) が、まさに customers テーブルと orders テーブルを繋ぐリレーションシップを定義している部分です。この FOREIGN KEY こそが、次のセクションで解説する「外部キー」の正体です。

主キーと外部キーの違いを明確に理解しよう

リレーションシップをSQLで表現する際に PRIMARY KEY と FOREIGN KEY という2つのキーワードが登場しました。この2つの「キー（鍵）」の違いを理解することが、テーブルの親子関係を把握する上で極めて重要です。

一言でいうと、主キーは「自分自身を特定するための鍵」、外部キーは「相手（親）を指し示すための鍵」です。

注文テーブルの customer_id は、それ単体では「注文」を一意に特定できませんが、「どの顧客による注文なのか」を示すために、親である 顧客 テーブルの主キー customer_id を参照しています。これが外部キーの役割です。

ER図での見分け方

ER図の記法はいくつか種類がありますが、多くの場合、主キーと外部キーは以下のように明記されます。

• 主キー (PK): カラム名に下線が引かれていたり、(PK) という印が付いています。
• 外部キー (FK): 親テーブルの主キーと線で結ばれており、(FK) と印が付いていることが多いです。

[ Customer ]
----------
customer_id (PK)
name

   ↑
   │ (参照)
   │
[ Order ]
----------
order_id (PK)
customer_id (FK)
product_name

この図を見るだけで、「Orderテーブルのcustomer_idは、Customerテーブルのcustomer_idを参照している外部キーだな」ということが一目で分かります。

外部キーとは？親子関係をつなぐ“橋”の役割

前のセクションで、外部キーは「他のテーブルの主キーを参照するカラム」だと説明しました。これは、テーブル間に親子関係を築き、データの整合性を保つための非常に重要な仕組みです。外部キーは、親と子をつなぐ“橋”のような役割を果たします。

この “橋” があるおかげで、データベースは「存在しない親」を持つ「孤立した子」データが生まれるのを防ぐことができます。

例えば、「顧客」テーブルに存在しない customer_id を持つ「注文」データが登録できてしまうと、その注文が誰からのものか分からなくなってしまいます。これはデータベースの信頼性を損なう致命的な問題です。外部キーは、このような不正なデータが登録されるのを防ぐ門番のような役割も担っているのです。この「データの矛盾を防ぐ仕組み」のことを外部キー制約と呼びます。

外部キー制約の基本構文

外部キー制約は、子テーブルを作成する CREATE TABLE 文の中で定義します。具体的には、FOREIGN KEY 句と REFERENCES 句を使います。

FOREIGN KEY (自テーブルの外部キーカラム名) REFERENCES 親テーブル名(親テーブルの主キーカラム名);

再掲になりますが、orders テーブルのSQLを見てみましょう。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT, -- このカラムが外部キーになる
  product_name VARCHAR(100),
  
  -- ↓ここが外部キー制約の定義↓
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

この一行を追加するだけで、orders テーブルの customer_id には、customers テーブルの customer_id に存在する値しか登録できなくなります。これにより、データの一貫性が保証されるのです。

参照整合性制約とは？削除や更新時のルールを決める仕組み

外部キーを設定すると、「存在しない親」を参照するデータは作れなくなりました。しかし、ここで新たな疑問が生まれます。「すでに子データから参照されている親データを、削除したり更新したりしたい場合はどうなるのか？」という問題です。

例えば、ある顧客が退会したとします。このとき、その顧客の注文履歴（子データ）をどう扱うべきでしょうか。

1. 顧客情報と一緒に、過去の注文履歴もすべて削除するべきか？
2. 注文履歴は残しつつ、誰からの注文だったかという情報だけを消すべきか？
3. それとも、注文履歴が残っている限り、顧客情報の削除はできないようにするべきか？

このように、親テーブルのデータが変更（UPDATE）されたり削除（DELETE）されたりした際に、子テーブルのデータの一貫性を保つためのルールを定義する仕組みが「参照整合性制約」です。このルールは、外部キーを定義する際に ON DELETE や ON UPDATE という句を使って指定します。

参照動作の種類一覧

具体的にどのような動作をさせることができるのか、代表的なものを以下の表にまとめました。特に CASCADE と RESTRICT は、応用情報技術者試験の午後問題でも頻出する重要な選択肢です。

これらの動作を適切に設定することで、データの矛盾を防ぎながら、業務のルールに合わせた柔軟なデータベースを設計することができます。次のセクションでは、これらの動作をSQLで実際に体験してみましょう。

【SQL実例】4つの参照整合性制約を体験しよう

前のセクションで学んだ4つの参照動作（CASCADE, SET NULL, RESTRICT, SET DEFAULT）が、実際のデータベースでどのように振る舞うのかをSQLを動かしながら体験してみましょう。百聞は一見に如かずです。

サンプル準備

まず、親となる customers テーブルを作成し、3人の顧客データを登録しておきます。このテーブルを各パターンの参照元として使います。

-- 親テーブルの作成
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- サンプルデータの挿入
INSERT INTO customers VALUES (1, '田中太郎'), (2, '佐藤花子'), (3, '山田一郎');

① ON DELETE CASCADE（親を消したら子も削除）

ON DELETE CASCADE は、親レコードを削除すると、その親を参照している子レコードも自動的に削除（連鎖削除）される設定です。

-- CASCADE設定を持つ子テーブル
CREATE TABLE orders_cascade (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_name VARCHAR(100),
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE  -- この部分がCASCADE設定
);

-- 注文データを登録
INSERT INTO orders_cascade VALUES (101, 1, '商品A'), (102, 2, '商品B');

-- customer_id = 1 の田中さんを削除してみる
DELETE FROM customers WHERE customer_id = 1;

結果：このDELETE文を実行すると、customersテーブルから田中さんのデータが消えるだけでなく、orders_cascadeテーブルにあったorder_id = 101の注文データも自動的に削除されます。

② ON DELETE SET NULL（外部キーをNULLに）

ON DELETE SET NULL は、親レコードが削除されたとき、子レコードは削除せず、外部キーカラムの値だけを NULL に変更する設定です。
※この設定を使うには、外部キーカラムがNULL値を許可している必要があります。

-- SET NULL設定を持つ子テーブル
CREATE TABLE orders_setnull (
  order_id INT PRIMARY KEY,
  customer_id INT, -- NULLを許容
  product_name VARCHAR(100),
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE SET NULL -- この部分がSET NULL設定
);

-- 注文データを登録
INSERT INTO orders_setnull VALUES (201, 1, '商品A'), (202, 2, '商品B');

-- customer_id = 1 の田中さんを削除してみる
DELETE FROM customers WHERE customer_id = 1;

結果：customersテーブルから田中さんのデータは削除されますが、orders_setnullテーブルのorder_id = 201のレコードは残ります。ただし、そのレコードのcustomer_idカラムの値がNULLに変更されます。「注文履歴は残したいが、退会した顧客との紐付けは切りたい」場合に有効です。

③ ON DELETE RESTRICT / NO ACTION（親削除を禁止）

ON DELETE RESTRICT（またはNO ACTION）は、子テーブルに参照されているレコードが1件でも存在する限り、親レコードの削除をエラーにして禁止する、最も安全な設定です。

-- RESTRICT設定を持つ子テーブル
CREATE TABLE orders_restrict (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_name VARCHAR(100),
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE RESTRICT -- この部分がRESTRICT設定
);

-- 注文データを登録
INSERT INTO orders_restrict VALUES (301, 2, '商品B');

-- customer_id = 2 の佐藤さんを削除してみる
DELETE FROM customers WHERE customer_id = 2;

結果：このDELETE文を実行しようとすると、「外部キー制約に違反しています」というエラーが発生し、削除は失敗します。orders_restrictテーブルに佐藤さんの注文データが残っているためです。佐藤さんを削除するには、先に注文データを削除する必要があります。

④ ON DELETE SET DEFAULT（デフォルト値に置き換える）

ON DELETE SET DEFAULT は、親レコードが削除されたとき、子の外部キーをあらかじめ指定したDEFAULT値に置き換える設定です。

-- SET DEFAULT設定を持つ子テーブル
CREATE TABLE orders_setdefault (
  order_id INT PRIMARY KEY,
  customer_id INT DEFAULT 3, -- デフォルト値を山田さん(ID:3)に設定
  product_name VARCHAR(100),
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE SET DEFAULT -- この部分がSET DEFAULT設定
);

-- 注文データを登録
INSERT INTO orders_setdefault VALUES (401, 1, '商品A');

-- customer_id = 1 の田中さんを削除してみる
DELETE FROM customers WHERE customer_id = 1;

結果：田中さんのデータが削除されると、orders_setdefaultテーブルにあった注文（order_id = 401）のcustomer_idが、デフォルト値である3（山田さん）に自動で置き換わります。退会者の担当を特定のユーザーに引き継ぐ、といった業務で利用が考えられます。

ER図と外部キーの関係をまとめて理解│応用情報 午後試験の基礎

ここまで学んできた知識を繋ぎ合わせ、ER図と外部キーの関係性を一枚の絵として理解しましょう。データベース設計、特に応用情報技術者試験の午後問題などでは、この全体像を把握しているかどうかが問われます。

結論から言うと、ER図に描かれるテーブル間の「線」の正体は、SQLにおける「外部キー制約」です。

設計の思考プロセスは、以下のようになります。

1. ER図で関係性を定義する:
   まず、概念設計の段階で「顧客と注文は一対多の関係だな」というように、エンティティ（テーブル）同士の関係性をER図で可視化します。
   [顧客] 1 ----- N [注文]
2. テーブル設計に落とし込む:
   次に、ER図を元に具体的なテーブル構造（物理設計）を考えます。このとき、「注文テーブルに、どの顧客の注文かを示すためのカラムが必要だ」と考え、customer_idを追加します。
3. SQLで外部キー制約を実装する:
   最後に、テーブルを作成するSQL文で、FOREIGN KEY句を用いて「注文テーブルのcustomer_idは、顧客テーブルのcustomer_idを参照する」というルールを明確に定義します。これが外部キー制約の実装です。

この「ER図 → テーブル設計 → 外部キー制約 → データの整合性確保」という一連の流れを意識することが非常に重要です。ER図を描けるようになると、SQLが読めるようになり、逆にSQLを見ればシステムのデータ構造（ER図）が頭に思い浮かぶようになります。このスキルは、データベースを扱うすべてのエンジニアにとって必須の基礎知識と言えるでしょう。

よくあるミスと設計のコツ│データベースの午後試験 攻略ポイント

ER図と外部キーの概念を理解しても、実際の設計や試験問題で思わぬ落とし穴にはまってしまうことがあります。ここでは、初心者がやりがちなミスと、それを避けるための設計のコツをいくつか紹介します。

外部キーを設定し忘れて「孤立データ」が発生

最も基本的かつ重大なミスが、外部キー制約そのものを設定し忘れることです。これを忘れると、データベースは「存在しない顧客IDを持つ注文」のような、親とはぐれた孤立データ（迷子データ）の登録を許してしまいます。データの整合性が崩れる原因になるため、テーブル間の関連を定義したら、必ずSQLでFOREIGN KEY制約を実装しましょう。

多対多の関係を1対Nに誤解する

「学生」と「講義」のように、1人の学生が複数の講義を履修し、1つの講義には複数の学生が所属する関係は「多対多」です。これを無理に「一対多」で表現しようとすると、設計が破綻します。多対多の関係は、両者をつなぐ「中間テーブル（連関エンティティ）」を間に挟んで、2つの「一対多」の関係に分解するのが定石です。

[学生] 1 --- N [履修テーブル] N --- 1 [講義]

ER図の段階で「親・子」を明確にする

リレーションシップを考える際は、どちらが「1」でどちらが「多（N）」なのか、つまりどちらが親でどちらが子なのかを明確に意識することが重要です。一般的に、「○○を持つ側」が親、「○○に所属する側」が子になります。「顧客」は複数の「注文」を持つので、顧客が親です。この関係を逆にすると、外部キーを設定するテーブルが逆になり、正しくデータを表現できません。

`RESTRICT`を使うときは削除順序にも注意

データの安全性を最も高く保てるON DELETE RESTRICTですが、運用上の注意点があります。参照されている親データを削除したい場合、必ず先に子データをすべて削除（または外部キーを更新）する必要があります。この削除順序を考慮せずにアプリケーションを組むと、データ削除処理で意図しないエラーが多発する原因となります。

まとめ：ER図と外部キーを理解すればデータが“つながる”

この記事では、データベース設計の基礎であるER図、リレーションシップ、主キーと外部キーの違い、そして参照整合性制約について、SQLの実例を交えながら解説してきました。最後に、それぞれの概念がどのような役割を果たしているのかを、もう一度シンプルに振り返ってみましょう。

• ER図は「設計の地図」
  システム全体のデータの構造と関係性を可視化し、設計のブレを防ぎます。
• 外部キーは「データの交通ルール」
  テーブル同士を正しくつなぎ、「存在しない親」を持つ「迷子のデータ」が生まれないようにします。
• 参照整合性は「安全なデータ管理の仕組み」
  親データの変更や削除が発生した際に、関連する子データが矛盾した状態にならないよう、振る舞いのルールを定めます。

これらの概念は、一見すると複雑に感じるかもしれません。しかし、この3つの関係性を一度理解してしまえば、どんなに複雑なシステムのデータベースでも、その構造を読み解く力が身につきます。応用情報技術者試験の対策はもちろん、実務でデータベースを扱う上での強力な土台となるはずです。

次のステップとしては、テーブルをより効率的に整理するための「正規化」や、より複雑な関係性を表現する「スーパータイプ／サブタイプ」といった概念を学んでいくと、さらに理解が深まるでしょう。