PostgreSQLで多対多リレーションを実装する方法

PostgreSQLにおける多対多リレーションの実装

以下、例を用いて説明します。

例：

生徒と科目を多対多で関連付ける場合、以下の3つのテーブルを作成します。

• 生徒テーブル：

CREATE TABLE 生徒 (
  生徒ID SERIAL PRIMARY KEY,
  氏名 VARCHAR(255) NOT NULL
);

CREATE TABLE 科目 (
  科目ID SERIAL PRIMARY KEY,
  科目名 VARCHAR(255) NOT NULL
);

CREATE TABLE 生徒科目 (
  生徒ID INTEGER NOT NULL REFERENCES 生徒(生徒ID),
  科目ID INTEGER NOT NULL REFERENCES 科目(科目ID),
  PRIMARY KEY (生徒ID, 科目ID)
);

多対多リレーションの操作

中間テーブルを使用して、多対多リレーションの操作を実行できます。以下、いくつかの例を示します。

• 生徒に科目を追加

INSERT INTO 生徒科目 (生徒ID, 科目ID) VALUES (1, 2);

このクエリは、生徒ID 1の生徒に科目ID 2の科目を追加します。

• 生徒が履修している科目一覧を取得

SELECT 科目名
FROM 科目
JOIN 生徒科目 ON 科目.科目ID = 生徒科目.科目ID
WHERE 生徒科目.生徒ID = 1;

• 科目を受講している生徒一覧を取得

SELECT 氏名
FROM 生徒
JOIN 生徒科目 ON 生徒.生徒ID = 生徒科目.生徒ID
WHERE 生徒科目.科目ID = 2;

補足

• 中間テーブルには、追加のカラムを追加できます。例えば、履修開始日や終了日などの情報を含めることができます。
• 多対多リレーションを表現する別の方法として、結合テーブルを使用する方法もあります。しかし、中間テーブルの方がより一般的で、柔軟性が高く、多くの場合、結合テーブルよりも効率的です。

生徒と科目を多対多で関連付ける場合のサンプルコードは以下の通りです。

CREATE TABLE 生徒 (
  生徒ID SERIAL PRIMARY KEY,
  氏名 VARCHAR(255) NOT NULL
);

CREATE TABLE 科目 (
  科目ID SERIAL PRIMARY KEY,
  科目名 VARCHAR(255) NOT NULL
);

CREATE TABLE 生徒科目 (
  生徒ID INTEGER NOT NULL REFERENCES 生徒(生徒ID),
  科目ID INTEGER NOT NULL REFERENCES 科目(科目ID),
  PRIMARY KEY (生徒ID, 科目ID)
);

INSERT INTO 生徒科目 (生徒ID, 科目ID) VALUES (1, 2);

SELECT 科目名
FROM 科目
JOIN 生徒科目 ON 科目.科目ID = 生徒科目.科目ID
WHERE 生徒科目.生徒ID = 1;

SELECT 氏名
FROM 生徒
JOIN 生徒科目 ON 生徒.生徒ID = 生徒科目.生徒ID
WHERE 生徒科目.科目ID = 2;

説明

• 上記のコードは、PostgreSQLで多対多リレーションを実装するための基本的な例です。
• 実際のアプリケーションでは、より複雑なクエリや操作が必要になる場合があります。
• 詳細については、PostgreSQLの公式ドキュメントを参照してください。

PostgreSQLにおける多対多リレーションの実装：他の方法

結合テーブルを使用する方法は、中間テーブルを使用するよりもシンプルで、クエリが分かりやすくなる場合があります。

CREATE TABLE 生徒 (
  生徒ID SERIAL PRIMARY KEY,
  氏名 VARCHAR(255) NOT NULL
);

CREATE TABLE 科目 (
  科目ID SERIAL PRIMARY KEY,
  科目名 VARCHAR(255) NOT NULL
);

CREATE TABLE 生徒科目 (
  生徒ID INTEGER NOT NULL REFERENCES 生徒(生徒ID),
  科目ID INTEGER NOT NULL REFERENCES 科目(科目ID)
);

INSERT INTO 生徒科目 (生徒ID, 科目ID) VALUES (1, 2);

SELECT 科目名
FROM 科目
WHERE 科目ID IN (
  SELECT 科目ID
  FROM 生徒科目
  WHERE 生徒ID = 1
);

SELECT 氏名
FROM 生徒
WHERE 生徒ID IN (
  SELECT 生徒ID
  FROM 生徒科目
  WHERE 科目ID = 2
);

利点

• 中間テーブルを使用するよりもシンプルで、クエリが分かりやすい。

欠点

• 中間テーブルを使用するよりも冗長になる可能性がある。
• 結合クエリが複雑になる可能性がある。

JSONBデータ型を使用する方法は、柔軟性が高く、複雑な多対多リレーションを表現するのに適しています。

CREATE TABLE 生徒 (
  生徒ID SERIAL PRIMARY KEY,
  氏名 VARCHAR(255) NOT NULL,
  科目 JSONB
);

UPDATE 生徒
SET 科目 = jsonb_array_append(科目, jsonb_build_object('科目ID', 2))
WHERE 生徒ID = 1;

SELECT 科目
FROM 生徒
WHERE 生徒ID = 1;

SELECT 生徒ID
FROM 生徒
WHERE 科目 @> (jsonb_build_object('科目ID', 2));

• 中間テーブルを使用する必要がない。

• パフォーマンスが低下する可能性がある。

トリガーを使用する方法は、多対多リレーションの整合性を保つのに役立ちます。

CREATE TRIGGER生徒科目_トリガー
BEFORE INSERT OR DELETE ON 生徒科目
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    UPDATE 科目
    SET 履修生徒数 = 履修生徒数 + 1
    WHERE 科目ID = NEW.科目ID;
  ELSE
    UPDATE 科目
    SET 履修生徒数 = 履修生徒数 - 1
    WHERE 科目ID = OLD.科目ID;
  END IF;
END;

INSERT INTO 生徒科目 (生徒ID, 科目ID) VALUES (1, 2);

SELECT 科目名
FROM 科目
WHERE 科目ID IN (
  SELECT 科目ID
  FROM 生徒科目
  WHERE 生徒ID = 1
);

SELECT 氏名
FROM 生徒