Constraints / JOIN：把資料有效性與關係留在 Database

Learning outcomes

• 能說明 PK/FK/UNIQUE/NOT NULL/CHECK 的責任。
• 能設計一對多 relation。
• 能讀 INNER JOIN / LEFT JOIN。
• 能辨識 row multiplication 與 missing relation。

1. Constraint 是 persistent invariant

CREATE TABLE courses (
  id BIGINT PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  score INT
    CHECK (
      score IS NULL
      OR score BETWEEN 0 AND 100
    )
);

即使某個 client 忘了驗證 score，Database 仍拒絕 999。

2. Foreign key 表達 relation

CREATE TABLE lessons (
  id BIGINT PRIMARY KEY,
  course_id BIGINT NOT NULL
    REFERENCES courses(id),
  title TEXT NOT NULL
);

Lesson 的 course_id 必須指向存在的 Course，除非 schema 明確允許其他語意。

3. INNER JOIN

SELECT
  courses.title AS course_title,
  lessons.title AS lesson_title
FROM courses
JOIN lessons
  ON lessons.course_id = courses.id;

只保留兩邊能 match 的 rows。

4. LEFT JOIN

SELECT
  courses.title,
  lessons.title
FROM courses
LEFT JOIN lessons
  ON lessons.course_id = courses.id;

即使某 course 沒 lesson，左邊 course 仍會保留，右邊欄位為 NULL。

5. JOIN 可能放大 rows

一個 course 有 20 lessons，JOIN 後 course 欄位會出現 20 次。這不是 duplicate bug，而是一對多 relation 的結果。做 aggregate 時要理解 cardinality。

Project checkpoint：Course Workspace v6

courses
  1 ──────< many lessons

constraints:
  course.slug UNIQUE
  lesson.course_id FK
  score 0..100

query:
  course + lesson count

SELECT
  c.id,
  c.title,
  COUNT(l.id) AS lesson_count
FROM courses c
LEFT JOIN lessons l
  ON l.course_id = c.id
GROUP BY c.id, c.title;

Debug evidence：JOIN 後數量突然變多

先畫 cardinality。A table 一列對 B table 幾列？JOIN predicate 是否正確？是否因多對多 bridge table 再度乘開？不要第一時間用 DISTINCT 隱藏模型問題。