SQLで1対1、1対多、多対多の関係を実装する

1対1の関係 (One-to-One)

• 実装方法:
  1. 主キーと外部キー: 2つのテーブルにそれぞれ主キーを作成します。一方のテーブルの主キーをもう一方のテーブルの外部キーとして参照します。
  2. 一意制約: 外部キー列に一意制約を設定することで、1対1の関係を保証します。

例:

• テーブル: customers (顧客)、addresses (住所)
• 関係: 1人の顧客は1つの住所を持つ
• SQL:

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

CREATE TABLE addresses (
  address_id INT PRIMARY KEY,
  street VARCHAR(100),
  city VARCHAR(50),
  country VARCHAR(50),
  customer_id INT UNIQUE FOREIGN KEY REFERENCES customers(customer_id)
);

• 実装方法:
  1. 主キーと外部キー: 主キーを持つテーブルから、外部キーを持つテーブルへ参照を設定します。
  2. 外部キーの重複: 外部キー列の重複を許可することで、1対多の関係を表現します。

• テーブル: orders (注文)、order_items (注文項目)
• 関係: 1つの注文は複数の注文項目を持つ

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(orde   r_id)
);

• 実装方法:
  1. 中間テーブル: 2つのエンティティの間にある中間テーブルを作成します。
  2. 外部キー: 中間テーブルに両方のエンティティの主キーを外部キーとして参照します。

• テーブル: students (学生)、courses (コース)
• 関係: 複数の学生が複数のコースを受講できる

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

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

CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student   _id),
  FOREIGN KEY (course_id) REFERENCES courses(cour   se_id)
);

SQLで表すエンティティ関係の例

例: 顧客と住所

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

CREATE TABLE addresses (
  address_id INT PRIMARY KEY,
  street VARCHAR(100),
  city VARCHAR(50),
  country VARCHAR(50),
  customer_id INT UNIQUE FOREIGN KEY REFERENCES customers(customer_id)
);

• customers テーブルと addresses テーブルは、customer_id を介して1対1の関係になっています。
• addresses テーブルの customer_id 列は、customers テーブルの customer_id 列を参照する外部キーであり、一意制約が設定されているため、1人の顧客に1つの住所しか関連付けられません。

例: 注文と注文項目

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(orde   r_id)
);

• orders テーブルと order_items テーブルは、order_id を介して1対多の関係になっています。
• order_items テーブルの order_id 列は、orders テーブルの order_id 列を参照する外部キーであり、重複が許可されているため、1つの注文に複数の注文項目が関連付けられます。

例: 学生とコース

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

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

CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student   _id),
  FOREIGN KEY (course_id) REFERENCES courses(cour   se_id)
);

• students テーブルと courses テーブルは、student_courses 中間テーブルを介して多対多の関係になっています。
• student_courses テーブルは、両方のテーブルの主キーを外部キーとして参照し、複合主キーを設定することで、複数の学生が複数のコースを受講できることを表現しています。

埋め込み (Embedding)

• 利点: クエリがシンプルになり、パフォーマンスが向上する可能性があります。
• 欠点: データの正規化が低下し、テーブルの構造が複雑になる可能性があります。

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50),
  street VARCHAR(100),
  city VARCHAR(50),
  country VARCHAR(50)
);

継承 (Inheritance)

• 利点: データの重複を減らし、テーブルの構造がシンプルになります。
• 欠点: データベースの管理が複雑になる可能性があります。

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

CREATE TABLE managers (
  employee_id INT PRIMARY KEY,
  title VARCHAR(50),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

連結テーブル (Junction Table)

• 利点: テーブルの数が減り、管理がシンプルになります。

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

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

CREATE TABLE student_courses (
  student_id INT PRIMARY KEY,
  course_id INT,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);