MySQL テーブルのリレーションシップと外部キー

データベースは通常、組織性と効率性を維持するために、関連する情報を異なるテーブルに保存します。例えば、あるデータベースには顧客の詳細情報を保存するテーブルと、彼らの注文（オーダー）を保存する別のテーブルがあるかもしれません。これらの分散した情報を接続し、データの完全なビューを再構築するには、テーブル間にリレーションシップ（Relationships）を構築する必要があります。これらのリレーションシップは、主に外部キー（Foreign Keys）を使用して管理されます。

1. リレーショナルデータベース設計の原則

リレーショナルデータベースは、各テーブルが特定のタイプの情報を保持する「関連テーブル」の概念に基づいて構築されています。この手法は正規化（Normalization）と呼ばれ、データの冗長性を減らし、データの整合性（インテグリティ）を向上させるのに役立ちます。すべての顧客と注文の詳細を一つの大きなテーブルに保存する（これでは顧客が注文するたびに顧客情報が重複してしまいます）代わりに、データを論理的なユニットに分割します。

1.1 プライマリキー（Primary Keys）と一意識別

外部キーを探索する前に、プライマリキーの役割を復習することが重要です。プライマリキーは、テーブル内の各行（レコード）を一意に識別するカラム（またはカラムのセット）です。これにより、各レコードがユニークであり、曖昧さなく参照できることが保証されます。

例えば、Customers（顧客）テーブルを考えてみましょう。

ここでは、customer_id がプライマリキーです。各 customer_id は一意であり、特定の顧客を識別します。

1.2 リレーションシップの必要性

一人の顧客が複数の注文を出すシナリオを想像してください。もしすべての注文情報を直接 Customers テーブルに保存しようとすると、同じ顧客に対して複数の行を作成する（customer_id プライマリキーの一意性に違反する）か、クエリが非常に困難な方法（例えば、一つのカラムの中にカンマ区切りのリストとして保存する）で注文詳細を保存しなければなりません。どちらの手法も効率的ではなく、優れたデータベース設計の原則にも反します。

代わりに、注文固有の詳細を保存するために別の Orders（注文）テーブルを作成します。

この Orders テーブルは独自のプライマリキー order_id を持っています。しかし、現時点ではどの顧客がどの注文を出したかを知る直接的な方法がありません。ここで外部キーが必要不可欠になります。

2. 外部キー（Foreign Keys）：テーブル同士を接続する

外部キーは、一つのテーブル内のカラム（またはカラムのセット）であり、別のテーブルのプライマリキーを参照します。外部キーを含むテーブルは参照テーブル（referencing table）（または子テーブル）、それが参照するプライマリキーを含むテーブルは被参照テーブル（referenced table）（または父テーブル）と呼ばれます。

外部キーの目的は、二つのテーブル間に接続を確立し、参照整合性（Referential Integrity）を強制することです。参照整合性は、テーブル間のリレーションシップが一貫性を保つことを保証します。例えば、存在しない顧客に対して注文を作成することはできません。また、システム内にその顧客に関連付けられた注文が残っている場合、その顧客を削除することもできません（これらのケースに対して特定の動作ルールを定義していない限り）。

2.1 外部キーの仕組み

外部キーの概念を Customers と Orders テーブルに統合してみましょう。注文を顧客にリンクするために、Orders テーブルに Customers テーブルの customer_id を保持するカラムを追加します。Orders テーブル内のこの新しいカラムが外部キーとなります。

修正後の Orders テーブル：

この修正後の Orders テーブルにおいて、customer_id カラムは Customers テーブルの customer_id（プライマリキー）を参照する外部キーです。

• 注文 101 は顧客 1（Alice Smith）によって出されました。
• 注文 102 は顧客 2（Bob Johnson）によって出されました。
• 注文 103 も顧客 1（Alice Smith）によって出されました。これは、一つのプライマリキーが複数の外部キー値によって参照できることを示しています。

2.2 外部キーの定義

MySQLでテーブルを作成または修正する際、外部キー制約（コンストレイント）を明確に定義する必要があります。これには、子テーブル（参照テーブル）内のカラムと、父テーブル（被参照テーブル）内のプライマリキーカラムを指定することが含まれます。

子テーブル内の外部キーカラムは、父テーブルで参照するプライマリキーカラムと互換性のあるデータ型（データタイプ）を持つ必要があります。例えば、Customers テーブルの customer_id が INT であれば、Orders テーブルの customer_id も INT でなければなりません。

3. リレーションシップの種類

外部キーは、テーブル間に異なるタイプのリレーションシップをもたらします。

3.1 1対多のリレーションシップ（One-to-Many Relationship）

これは最も一般的なリレーションシップのタイプです。父テーブルの1行が、子テーブルの複数行と関連付けられます。

• 例： 一人の顧客は多くの注文を出すことができます。（Customers (父) から Orders (子)）。外部キー（customer_id）は Orders テーブルに配置されます。

3.2 1対1のリレーションシップ（One-to-One Relationship）

（比較的稀ですが、存在します）
父テーブルの1行が、子テーブルのちょうど1行と関連付けられます。これは通常、パフォーマンスやセキュリティ上の理由で非常に幅の広い（カラムが多い）テーブルを分割する場合や、オプションの属性を保存するために使用されます。

• 例： Users（ユーザー）テーブルと User_Profiles（ユーザープロファイル）テーブル。各ユーザーは一つのプロファイルのみを持ちます。外部キーはどちらのテーブルにも配置でき、もう一方のテーブルのプライマリキーを参照します。通常、1対1のルールを強制するために外部キーカラムには UNIQUE 制約を付与します。あるいは、子テーブルのプライマリキーが外部キーを兼ねることもあります。

3.3 多対多のリレーションシップ（Many-to-Many Relationship）

最初のテーブルの1行が二番目のテーブルの複数行と関連付けられ、同時に二番目のテーブルの1行も最初のテーブルの複数行と関連付けられる状態です。

• 例： 多くの学生が多くのコース（講義）を履修でき、一つのコースには多くの学生が参加できます。

このリレーションシップは、単一の外部キーで直接実現することはできません。代わりに、二つのテーブルからの外部キーを含む関連テーブル（associative table）（中間テーブル、ジョインテーブル、またはブリッジテーブルとも呼ばれます）が必要です。

Students（学生）と Courses（コース）の場合、Enrollments（登録）テーブルを作成し、そこに student_id（Students への外部キー）と course_id（Courses への外部キー）を含めます。Enrollments テーブルのプライマリキーは、通常 student_id と course_id で構成される複合キー（composite key）になります。

4. 参照整合性と外部キー制約（Constraint）

外部キー制約は、参照整合性を維持するために不可欠です。これは、テーブル間のリレーションシップが有効であることを保証することを意味します。外部キーを定義すると、MySQLはこれらのリンクを破壊するような操作を防ぐためのルールを課します。

外部キーに対して定義される一般的なアクションルールは以下の通りです：

• ON DELETE CASCADE（カスケード削除）： 父テーブルの行が削除された場合、子テーブル内の対応するすべての行も自動的に削除されます。
• ON UPDATE CASCADE（カスケード更新）： 父テーブルの行のプライマリキーが更新された場合、子テーブル内の対応する外部キー値も自動的に更新されます。
• ON DELETE SET NULL（削除時にNULL設定）： 父テーブルの行が削除された場合、子テーブルの外部キーカラムに NULL が設定されます。これには、外部キーカラムが NULL を許容している必要があります。
• ON UPDATE SET NULL（更新時にNULL設定）： 父テーブルの行のプライマリキーが更新された場合、子テーブルの外部キーカラムに NULL が設定されます。
• ON DELETE RESTRICT（削除制限、通常はデフォルト）： 依存する子行が存在する場合、父行の削除を阻止します（エラーが発生します）。
• ON UPDATE RESTRICT（更新制限、デフォルト）： 依存する子行が存在する場合、父のプライマリキーの更新を阻止します。

5. 実践的な例とデモンストレーション

図書管理システムに基づいた簡略化されたシナリオを使用して、外部キーの作成と挙動をデモします。Authors（著者）と Books（書籍）の二つのテーブルを作成します。

例 1：外部キーを持つテーブルの作成

まず、Authors テーブル（父テーブル）を作成します。

CREATE TABLE Authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_year INT
);

次に、Books テーブル（子テーブル）を作成し、外部キーを定義します。

CREATE TABLE Books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    publication_year INT,
    author_id INT, -- これが外部キーになります
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
    -- Books 内の 'author_id' を Authors 内の 'author_id' (プライマリキー) にリンクします
    ON DELETE RESTRICT -- デフォルト動作：書籍が存在する場合、著者の削除を阻止します
    ON UPDATE CASCADE -- author_id が変更された場合（プライマリキーでは稀）、Books テーブルでも同期更新します
);

例 2：データの挿入と参照整合性のデモ

今度は、存在しない著者に対して本を挿入しようとしてみます。

-- Authors テーブルに存在しない author_id に対して本を挿入しようとする試み
INSERT INTO Books (title, publication_year, author_id) VALUES('存在しない本', 2020, 99);

この INSERT ステートメントは失敗し、Cannot add or update a child row: a foreign key constraint fails のようなエラーが表示されます。これは author_id = 99 が Authors テーブルに存在しないためです。これが実際に動作している参照整合性であり、「孤児レコード（orphaned records）」の発生を防いでいます。

例 3：ON DELETE RESTRICT のデモ

関連する書籍がまだ残っている著者を削除しようとしてみます。

-- Stephen King の author_id が 1 であり、彼の本がすでに Books テーブルにあると仮定
DELETE FROM Authors WHERE author_id = 1;

この DELETE ステートメントも同様に失敗し、Cannot delete or update a parent row: a foreign key constraint fails というエラーが出ます。Books テーブル内に author_id = 1 を参照している本が存在し、かつ ON DELETE RESTRICT を指定しているためです。

この著者を正常に削除するには、まず以下のいずれかを行う必要があります：

1. Books テーブルから、その著者が書いたすべての本を削除する。
2. あるいは、それらの本の author_id を別の既存の著者に変更する（適用可能な場合）。

もし外部キー作成時に ON DELETE CASCADE を定義していれば、著者を削除すると自動的に Books テーブルからその著者のすべての本が削除されます。