【SQL & データベース入門】テーブルのリレーションと外部キーとは？データの関連付けを解説

2025.02.07

リレーショナルデータベースの基本概念

リレーショナルデータベースとは

リレーショナルデータベース（RDB）は、データをテーブルとして管理し、それぞれのテーブル間の関係（リレーション）を活用してデータを効果的に管理するデータベースの一種です。テーブル間の関係を適切に設計することで、データの重複や不整合を防ぎ、検索や操作が効率化されます。

リレーションの種類

リレーションには主に次の3種類があります：

  • 1対1の関係（One-to-One）：1つのレコードに対して、別のテーブルの1つのレコードが対応する関係。
  • 1対多の関係（One-to-Many）：1つのレコードに対して、別のテーブルに複数のレコードが関連する関係。
  • 多対多の関係（Many-to-Many）：複数のレコードが別のテーブルの複数のレコードに関連する関係。

外部キー（FOREIGN KEY）とは

外部キーの定義

外部キー（FOREIGN KEY）は、あるテーブルの列が別のテーブルの主キー（PRIMARY KEY）を参照するための制約です。外部キーを使うことで、異なるテーブル間のデータの整合性を保つことができます。

外部キーの役割

  • データの整合性の保証：関連するデータが存在することを保証します。
  • データの参照整合性：削除や更新時に影響するデータを制御します。

外部キーの基本構文

CREATE TABLE テーブル名 (
    列名 データ型,
    列名 データ型,
    FOREIGN KEY (外部キーの列) REFERENCES 参照先テーブル(参照先の列)
);

リレーションの設定方法

1対1の関係の例

1対1の関係は、あるテーブルのレコードに対して別のテーブルの1つのレコードが対応する場合に使います。たとえば、社員情報とその詳細情報が1対1で対応する場合です。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employee_details (
    detail_id INT PRIMARY KEY,
    employee_id INT UNIQUE,
    address VARCHAR(100),
    phone_number VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
  • employee_details テーブルの employee_id は employees テーブルの主キーを参照しています。
  • UNIQUE 制約により、1対1の関係が保たれます。

1対多の関係の例

1対多の関係は、1つのレコードに対して別のテーブルの複数のレコードが関連する場合に使います。たとえば、1人の顧客が複数の注文を持つ場合です。

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
  • orders テーブルの customer_id が customers テーブルの主キーを参照し、1人の顧客に対して複数の注文を管理できます。

多対多の関係の例

多対多の関係は、中間テーブルを使用して2つのテーブルをつなぐことで表現されます。たとえば、学生とコースが多対多の関係を持つ場合です。

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
  • 中間テーブル enrollments を使用することで、1人の学生が複数のコースに登録でき、1つのコースにも複数の学生が登録できる関係を表現しています。

ON DELETEとON UPDATEのオプション

オプションの概要

外部キーを使用する際、関連するデータが削除または更新されたときの動作を指定することができます。

  • ON DELETE CASCADE：親テーブルのデータが削除されたときに、子テーブルの関連するデータも自動的に削除されます。
  • ON DELETE SET NULL：親テーブルのデータが削除されたとき、子テーブルの外部キーが NULL に設定されます。
  • ON DELETE RESTRICT：親テーブルに関連する子テーブルのデータがある場合、削除を制限します。

使用例

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

この例では、customers テーブルのデータが削除されると、それに関連するすべての orders のデータも自動的に削除されます。

実際の活用例

ECサイトでのリレーションの活用

ECサイトでは、顧客、注文、商品などの情報が複数のテーブルに分けて保存され、外部キーを用いてリレーションを構築します。たとえば、顧客が注文した商品情報を効率的に取得するには、適切に設計されたリレーションが不可欠です。

学校管理システムでのリレーションの活用

学生、コース、成績といった情報を管理する際に、多対多のリレーションを使用することで、学生が複数のコースに登録し、各コースの成績を記録することができます。

リレーション設計時の注意点

適切なリレーションの選択

データ間の関係を正確に理解し、1対1、1対多、多対多のいずれのリレーションが適切かを見極めることが重要です。

外部キーの制約設定

外部キー制約を適切に設定することで、データの不整合を防ぎ、システムの信頼性を向上させることができます。

パフォーマンスへの影響

外部キーを多用しすぎると、データ操作時のパフォーマンスに影響を与える可能性があります。必要に応じてインデックスを設定し、効率的にデータを操作できるようにしましょう。

まとめ

テーブル間のリレーションと外部キーの設計は、リレーショナルデータベースの基盤となる重要な要素です。適切なリレーションを構築し、外部キー制約を正しく設定することで、データの整合性を保ちながら効率的なデータ操作が可能になります。実際に設計を行いながら、リレーションの重要性を体感することで、より高度なデータベース設計のスキルを身につけられます。