テーブル enrollments を作って学生とコースをつなげる

ちょっとだけ細かい話を続けるよ。MANY-TO-MANY 構造を作る手順を一緒にしっかり見ていこう。準備はいい？

学生とコースの MANY-TO-MANY 関係は、1つのテーブルだけじゃ表現できないんだ。1人の学生は複数のコースに登録できるし、1つのコースも複数の学生が受講できるからね。

この問題を解決するために、中間テーブル enrollments を作るよ。ここにはどの学生がどのコースに登録してるかの情報を保存する。これでデータの整合性も守れるし、例えば登録日を追加したり、機能拡張も簡単になるんだ。

テーブル enrollments はどんな感じ？

もう知ってると思うけど、enrollments テーブルは students と courses テーブルをつなぐ中心になるよ。構造はこんな感じ：

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- レコードのユニークID
    student_id INT REFERENCES students(student_id), -- studentsテーブルへの外部キー
    course_id INT REFERENCES courses(course_id),    -- coursesテーブルへの外部キー
    enrollment_date DATE DEFAULT CURRENT_DATE   -- 学生がコースに登録された日
);

1行ずつ見ていこう：

• enrollment_id: これは各レコードのユニークなIDだよ。コースに登録された学生は1人ずつちゃんと識別できないとね。
• student_id: どの学生が登録されたかを示す。これは students テーブル（カラム student_id）への外部キーだよ。
• course_id: どのコースに学生が登録されたかを示す。このカラムは courses テーブル（カラム course_id）とつながってる。
• enrollment_date: 登録日を示す便利なカラム。DEFAULT CURRENT_DATE を使って、レコード作成時に自動で今日の日付が入るようにしてるよ。

students と courses テーブルを作る

次に進む前に、students と courses テーブルがもうあるか確認しよう：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,      -- 学生のユニークID
    name TEXT NOT NULL,                 -- 学生の名前
    email TEXT NOT NULL UNIQUE          -- 学生のメールアドレス（重複禁止）
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,       -- コースのユニークID
    title TEXT NOT NULL,                -- コース名
    description TEXT,                   -- コースの説明
    start_date DATE                     -- コース開始日
);

ここでは、例えば学生のメールアドレスをユニークにしたり、コースの説明を courses テーブルに追加したり、便利な情報も入れてるよ。

全部つなげてみよう

テーブルが準備できたら、enrollments テーブルを作成しよう：

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- 登録のユニークID
    student_id INT NOT NULL REFERENCES students(student_id), -- 外部キー
    course_id INT NOT NULL REFERENCES courses(course_id),    -- 外部キー
    enrollment_date DATE DEFAULT CURRENT_DATE   -- 登録日
);

テーブルにデータを入れる

テーブルはできたけど、データがないと寂しいよね。学生、コース、登録データを追加しよう：

学生の追加：

INSERT INTO students (name, email)
VALUES
    ('Alex Lin', 'alex.lin@example.com'),
    ('Maria Chi', 'maria.chi@example.com'),
    ('Otto Song', 'otto.song@example.com');

コースの追加：

INSERT INTO courses (title, description, start_date)
VALUES
    ('プログラミングの基礎', '初心者向けプログラミングコース。', '2023-11-01'),
    ('データベース', 'SQLとリレーショナルデータベースを学ぶ。', '2023-11-15'),
    ('ウェブ開発', 'ウェブサイトとウェブアプリの作成。', '2023-12-01');

登録データの追加：

INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 1), -- Alex Lin が「プログラミングの基礎」に登録
    (1, 2), -- Alex Lin が「データベース」に登録
    (2, 2), -- Maria Chi が「データベース」に登録
    (3, 3); -- Otto Song が「ウェブ開発」に登録

ここで student_id と course_id は、それぞれのテーブルのIDと一致してるよ。

クエリで関係をチェックしよう

全ての登録データを取得：

SELECT e.enrollment_id, s.name AS student_name, c.title AS course_title, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id;

結果：

自分でやってみよう課題

他にも学生やコースを追加して、enrollments テーブルに登録してみて。例えば「機械学習」というコースを追加して、1～2人の学生を登録してみよう。上のクエリで結果を確認してね。

よくあるミス

外部キーや中間テーブルを使うとき、よくハマるポイントがいくつかあるよ：

1. 親テーブルにレコードがない： enrollments に student_id や course_id を追加しようとして、それが students や courses テーブルに存在しない場合、エラーになるよ。外部キーはここを厳しくチェックしてる。

2. 削除時の整合性エラー： すでに enrollments テーブルで使われている学生やコースを ON DELETE CASCADE なしで消そうとすると、エラーになるよ。

3. 重複登録： 同じ学生を同じコースに何度も登録しないように注意してね（ビジネスロジックで許可されていない場合）。

これで、PostgreSQLで学生とコースの MANY-TO-MANY 関係を表現する実用的なモデルができたね。この構造は、学習管理システムやCRMなど、実際のアプリでめっちゃよく使われてるよ。次のレクチャーもがんばろう！

2

タスク

SQL SELF, レベル 20, レッスン 1

ロック未解除

`enrollments`テーブルの作成

`enrollments`テーブルの作成

オープン

2

タスク

SQL SELF, レベル 20, レッスン 1

ロック未解除

入学データの結合出力

入学データの結合出力

オープン