建立 enrollments 表格來連接學生和課程

再來我們要 geek 一下。來，讓我們也一樣仔細地走過建立 MANY-TO-MANY 結構的步驟。準備好了嗎？

MANY-TO-MANY 關聯在學生和課程之間沒辦法直接用一個表格來表示。一個學生可以選好幾門課，一門課也可以同時有好幾個學生。

為了解決這個問題，我們會建立一個中介表 enrollments，用來存放註冊資訊，也就是說，哪一組學生-課程配對存在。這樣不只可以確保資料完整性，還能很容易擴充功能，比如加上註冊日期。

enrollments 表格長怎樣？

你應該已經知道 enrollments 會是 students 跟 courses 兩個表格的核心連結。它的結構如下：

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- 唯一的記錄 ID
    student_id INT REFERENCES students(student_id), -- 指向 students 表的 foreign key
    course_id INT REFERENCES courses(course_id),    -- 指向 courses 表的 foreign key
    enrollment_date DATE DEFAULT CURRENT_DATE   -- 學生註冊課程的日期
);

來拆解一下每一行：

• enrollment_id：這是每一筆記錄的唯一識別碼。每個註冊課程的學生都要有獨一無二的 ID。
• student_id：代表哪個學生註冊。這是 foreign key，指向 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,      -- 學生唯一識別碼
    name TEXT NOT NULL,                 -- 學生姓名
    email TEXT NOT NULL UNIQUE          -- 學生 Email，避免重複
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,       -- 課程唯一識別碼
    title TEXT NOT NULL,                -- 課程名稱
    description TEXT,                   -- 課程描述
    start_date DATE                     -- 課程開始日期
);

注意這裡我們加了一些實用細節，比如學生的 email 要唯一，還有 courses 表裡的課程描述。

全部串起來

現在表格都準備好了，來建立 enrollments 表：

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- 註冊唯一 ID
    student_id INT NOT NULL REFERENCES students(student_id), -- foreign key
    course_id INT NOT NULL REFERENCES courses(course_id),    -- foreign key
    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'),
    ('網頁開發', '做網站和 web app。', '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 個學生進去。用上面的查詢來檢查結果。

常見錯誤

在操作 foreign key 和中介表時，有幾個常見陷阱：

1. 父表沒資料： 如果你想在 enrollments 插入一個 student_id 或 course_id，但 students 或 courses 表裡沒有這個 id，會直接噴錯。foreign key 會嚴格檢查這個。

2. 刪除時資料完整性錯誤： 如果你刪掉一個已經在 enrollments 用到的學生或課程，但沒設 ON DELETE CASCADE，也會出錯。

3. 重複紀錄： 要確定你不會讓同一個學生重複註冊同一門課，除非你的業務邏輯允許。

現在你已經有一個可以在 PostgreSQL 裡表示學生和課程 MANY-TO-MANY 關聯的模型了。這種結構在實際應用超常見，比如學習管理系統、CRM 等等。繼續下一堂課吧！