すべてのガイドに戻る
データベース設計··読了時間 6 分

1対多と多対多:SQLのリレーションパターンを解説

SQL における 1 対多と多対多のリレーションシップについて学びます。外部キー、中間テーブル、および実例を使用してそれぞれを実装する方法を確認します。

顧客から注文への外部キー、および複合主キーを持つ学生からコースへの結合テーブル
このガイドの内容
  1. SQLリレーションシップパターンの理解
  2. 1対多リレーションシップ
  3. 多対多のリレーションシップ
  4. 1対1リレーションシップ
  5. ERD におけるリレーションシップの表示方法
  6. 適切なリレーションシップパターンの選択
  7. 参考資料と詳細な読み物

SQL の例では、別のダイアレクトが指定されていない限り PostgreSQL を使用します。短いスニペットは、参照されているテーブルがすでに存在することを前提としています。適用する前に、ターゲットデータベースで DDL を検証してください。

SQLリレーションシップパターンの理解

リレーショナルデータベースは、テーブル間の接続からその強みを得ています。小規模なアプリケーションを設計する場合でも、エンタープライズデータウェアハウスを設計する場合でも、正しいリレーションシップパターンを選択することで、データを効率的に保存、クエリ、および保守できるかどうかが決まります。3つの基本的なパターン—1対1, 1対多、および 多対多— 多くの一般的な関連をカバーします。再帰的および高次のリレーションシップには追加のモデリングが必要です。

このガイドでは、PostgreSQLの例を使用して各パターンを分解し、ジャンクションテーブルの役割を探り、これらのリレーションシップが実体関連図(ERD)にどのように表示されるかを示します。今すぐ独自のスキーマを視覚化したい場合は、以下を開いてください: Erd Go エディター およびサポートされているものを貼り付け CREATE TABLE 図を生成するための定義。

顧客と注文の間のERDリレーションシップにマッピングされた外部キーSQL
外部キーは単なる構文以上のものであり、ERD では、テーブルが互いにどのように依存しているかを説明するリレーションシップ線になります。

1対多リレーションシップ

A 1対多 (1:N) 関係は、データベース設計において最も一般的なパターンです。これは、 親 テーブルは、次の多くの行から参照される可能性があります: 子 テーブル、および非 NULL の子外部キーは 1 つの親を指します。NULL 許容の外部キーは、親のない子を許可します。

古典的な例: 部門と従業員

会社には多くの部署があり、各部署には複数の従業員が所属しています。ただし、すべての従業員は、どの時点においても正確に1つの部署にのみ所属します。

CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    location    VARCHAR(255),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id              SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    department_id   INT NOT NULL,
    hire_date       DATE DEFAULT CURRENT_DATE,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

主要な実装詳細

  • 外部キー (department_id) は常に 多 側—その employees この場合のテーブル。
  • ON DELETE RESTRICT 従業員がまだ割り当てられている部署を誤って削除するのを防ぎます。
  • ON UPDATE CASCADE 部署の主キーが変更された場合、すべての従業員行が自動的に更新されることを保証します。
  • インデックスの追加対象: department_id 結合や部門フィルターに役立ちます。代表的なデータと実行計画で効果を確認してください。

一対多データのクエリ

-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';

-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;

その他の日常的な1対多の例には以下が含まれます。 authors → blog posts, 顧客 → 注文、および カテゴリ → 製品.

多対多のリレーションシップ

A 多対多 (M:N) 関係は、次の行が存在する場合に成立します。 両方 側は、もう一方の側の複数の行に関連付けることができます。リレーショナルデータベースでは、これを単一の外部キーで直接表現することはできないため、...を導入します。 中間テーブル (別名 ブリッジ, 結合、または 関連 テーブル)。

古典的な例: 学生とコース

1人の学生が多くのコースに登録でき、各コースには多くの学生が所属できます。ジャンクションテーブル enrollments それらの間に位置し、両方の側への外部キーを保持します。

CREATE TABLE students (
    id          SERIAL PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    enrolled_on DATE DEFAULT CURRENT_DATE
);

CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    credits     SMALLINT NOT NULL DEFAULT 3,
    department  VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id  INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    course_id   INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade       CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

中間テーブルが必要な理由

  • The 複合主キー (student_id, course_id) では、学生とコースのペアごとに 1 行が許可されます。学期をまたいだ履修登録の繰り返しが必要な場合は、ビジネスキーに学期を含めるか、履修登録ごとにモデル化してください。
  • リレーションシップ自体に次のような追加属性を添付できます: grade または enrolled_at。これらはどちらのエンティティにも単独では属しません。
  • ON DELETE CASCADE 両方の外部キーに設定すると、学生またはコースを削除したときに、登録行が自動的にクリーンアップされることを意味します。

多対多データのクエリ

-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;

-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';

現実世界の多対多の例には次のようなものがあります users ↔ roles, 商品 ↔ タグ, actors ↔ movies、および orders ↔ products (経由の order_items 数量と価格も保存する中間テーブル)。

1対1リレーションシップ

A 1対1 (1:1) 関係は、テーブル A の各行がテーブル B の最大 1 行にマッピングされ、その逆も同様であることを意味します。このパターンは一般的ではありませんが、アクセス頻度の低いカラムや機密性の高いカラムを別のテーブルに分割する場合に役立ちます。

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(60) UNIQUE NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id     INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
    bio         TEXT,
    avatar_url  VARCHAR(500),
    birthdate   DATE,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

次のことに注意してください user_profiles.user_id 両方の〜です 主キー およびその 外部キー。この制約はデータベースレベルで一対一のマッピングを強制します。各ユーザーは最大で1つのプロフィール行を持つことができます。

ERD におけるリレーションシップの表示方法

実体関係図(ERD)は、テーブル間の線とシンボルを使用してカーディナリティを一目で伝えます。最も一般的な2つの表記法は次のとおりです: Crow’s Foot および Chen 記法。

リレーションシップCrow’s Foot シンボル読み方
1対1||—||両側に正確に 1 つ
1対多||—<左側に1つ、右側に多(カラスの足の端)
多対多>—<両側に多数あり、中間テーブルを介して実装されます

これらのテキスト記号は最大カーディナリティの略記です。オプショナリティ(任意性)には追加の 0 または 1 のマーカーが必要です。外部キー単体では、親が子を持つことを必須とはしません。

ERD ツールで多対多のリレーションシップをモデリングする場合、通常 2 つの実体テーブルとその間にある中間テーブルの 3 つのボックスが表示され、それぞれが 1 対多の線で接続されます。

この記事の SQL の例のいずれかを次へ貼り付けます: Erd Go エディター 外部キーの矢印とカーディナリティのインジケーターを備えた、自動生成された図を表示するために。

適切なリレーションシップパターンの選択

誤ったカーディナリティを選択すると、データの重複、複雑なクエリ、および整合性の不具合につながります。次の判定フレームワークを使用してください:

  • 側 A の 1 行は側 B の多くの行に関連付けることができますが、その逆はできませんか? → 一対多。B側に外部キーを配置します。
  • 両側の行が相手側の多くの行に関連付けることができますか? → 多対多。複合キーを持つ中間テーブルを作成します。
  • マッピングは厳密に 1:1 であり、テーブルの分離が必要ですか? → 一対一。外部キーと同じ主キーを使用します。

迷ったときは、まずスキーマをスケッチしてみましょう。素早く作成した ERD は、本番環境で制約エラーに遭遇するよりもはるかに早くリレーションシップの問題を明らかにします。次で図の構築を試してみてください: Erd Go エディター— 数秒で完了し、何時間ものデバッグを節約できます。

参考資料と詳細な読み物

もっと見る