skip to content
BlogZzz

[Learn] PostgreSQL 03：用 FOREIGN KEY 建立一對多關聯

/ 8 min read

Updated:
View more blogs with the tag postgresql, View more blogs with the tag sql, View more blogs with the tag database
Table of Contents

上一篇建立了 users table，並完成新增、查詢、修改與刪除資料

但實際系統不會只有一張 table

以部落格為例，一位使用者可以發表多篇文章，每一篇文章則只屬於一位作者，這就是常見的一對多關聯

這篇會沿用上一篇的 users table，再建立 posts table，練習以下內容

  • 用 FOREIGN KEY 保證文章一定有存在的作者
  • 用 INNER JOIN 同時查詢文章與作者
  • 用 LEFT JOIN 保留還沒有文章的使用者
  • 理解刪除作者時，關聯文章應該怎麼處理

一對多關聯怎麼保存

users 與 posts 的關係可以畫成這樣

users
  id = 1, Alice
       │
       ├── posts.author_id = 1, PostgreSQL 學習筆記
       └── posts.author_id = 1, 第一次使用 JOIN

一對多關聯不需要在 users row 裡保存所有 post id

做法是在多的那一側，也就是 posts table，加入 author_id 指向 users.id

  • users 是被參照的 parent table
  • posts 是保存 foreign key 的 child table
  • users.id 是 primary key
  • posts.author_id 是 foreign key

建立 posts table

建立 posts table，並讓 author_id 參照 users.id

CREATE TABLE public.posts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id bigint NOT NULL
    REFERENCES public.users (id)
    ON DELETE RESTRICT,
  title text NOT NULL,
  content text NOT NULL,
  published_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

REFERENCES public.users (id) 會建立 foreign key constraint

PostgreSQL 寫入或修改 author_id 時，會檢查對應的 users.id 是否存在，避免資料指向不存在的作者

NOT NULL 與 foreign key 解決的是不同問題

  • NOT NULL：文章不能沒有 author_id
  • FOREIGN KEY：author_id 必須指向存在的 user

這裡使用 ON DELETE RESTRICT，代表作者還有文章時，不允許直接刪除作者

確認 user id

先查看上一篇保留下來的 user

SELECT id, email, display_name
FROM public.users
ORDER BY id;

如果完全按照上一篇的順序操作，Alice 通常是 id = 1，Carol 通常是 id = 3

identity column 不保證 id 連續，因此實際新增文章前，還是應該以自己的查詢結果為準

新增關聯資料

替 Alice 新增兩篇文章

INSERT INTO public.posts (author_id, title, content, published_at)
VALUES
  (
    1,
    'PostgreSQL 學習筆記',
    '整理 table 與 constraint 的基本概念',
    now()
  ),
  (
    1,
    '第一次使用 JOIN',
    '把文章與作者資料一起查出來',
    now()
  )
RETURNING id, author_id, title, published_at;

如果嘗試使用不存在的 author_id，PostgreSQL 會拒絕寫入

INSERT INTO public.posts (author_id, title, content)
VALUES (999999, '找不到作者', '這筆資料不應該成功');

錯誤會類似

ERROR: insert or update on table "posts" violates foreign key constraint
DETAIL: Key (author_id)=(999999) is not present in table "users"

這就是 referential integrity

不管資料來自 psql、application 或 migration，都不能繞過相同的關聯規則

用 INNER JOIN 查詢文章與作者

posts 只保存 author_id，作者名稱仍然在 users

要把兩邊的資料組合起來，可以使用 JOIN

SELECT
  p.id,
  p.title,
  u.display_name AS author_name,
  p.published_at
FROM public.posts AS p
INNER JOIN public.users AS u
  ON u.id = p.author_id
ORDER BY p.id;
  • p 是 posts 的 alias
  • u 是 users 的 alias
  • ON u.id = p.author_id 是兩張 table 的連接條件

INNER JOIN 只會保留左右兩邊都符合條件的 row

因為 foreign key 已經保證每篇文章都有存在的作者，所以這個查詢可以取得每篇文章對應的作者名稱

FOREIGN KEY 與 JOIN 不是同一件事

  • FOREIGN KEY 負責保護資料關聯
  • JOIN 負責在查詢時組合資料

用 LEFT JOIN 保留沒有文章的 user

如果從 users 開始使用 INNER JOIN，沒有文章的 Carol 不會出現在結果中

想保留所有 user，可以改用 LEFT JOIN

SELECT
  u.id,
  u.display_name,
  p.id AS post_id,
  p.title
FROM public.users AS u
LEFT JOIN public.posts AS p
  ON p.author_id = u.id
ORDER BY u.id, p.id;

LEFT JOIN 會保留左邊 users 的所有 row

如果某位 user 沒有文章，post_id 與 title 會是 NULL

也可以進一步計算每位 user 的文章數量

SELECT
  u.id,
  u.display_name,
  COUNT(p.id) AS post_count
FROM public.users AS u
LEFT JOIN public.posts AS p
  ON p.author_id = u.id
GROUP BY u.id, u.display_name
ORDER BY u.id;

這裡使用 COUNT(p.id)，不是 COUNT(*)

沒有文章的 user 經過 LEFT JOIN 後仍會保留一個結果 row，但 p.id 是 NULL，因此 COUNT(p.id) 才會得到 0

刪除 parent row 會發生什麼事

Alice 還有文章時，嘗試刪除 Alice

DELETE FROM public.users
WHERE id = 1;

因為 foreign key 使用 ON DELETE RESTRICT，PostgreSQL 會拒絕刪除

ERROR: update or delete on table "users" violates foreign key constraint
DETAIL: Key (id)=(1) is still referenced from table "posts"

常見的刪除行為包括

  • ON DELETE RESTRICT：還有 child row 時拒絕刪除 parent row
  • ON DELETE CASCADE：刪除 parent row 時一起刪除相關 child row
  • ON DELETE SET NULL：刪除 parent row 時把 foreign key 改成 NULL

SET NULL 需要允許 author_id 為 NULL，不能同時保留目前的 NOT NULL

這些選項沒有固定答案，要根據資料的生命週期決定

文章通常需要保留作者或稽核紀錄，因此這個範例先選擇較保守的 RESTRICT，不讓刪除動作默默帶走其他資料

foreign key column 也需要 index

PostgreSQL 會替 users.id 的 primary key 建立 unique B-tree index，但不會自動替 child table 的 posts.author_id 建立 index

文章量增加後，下面兩種操作都經常需要依 author_id 找資料

  • 查詢某位作者的所有文章
  • 刪除 user 前檢查是否還有文章參照他

因此可以建立 index

CREATE INDEX posts_author_id_idx
ON public.posts (author_id);

資料量很小時不一定看得出差異，但在關聯欄位上建立符合查詢方式的 index，是實務上常見的做法

結論

  • 一對多關聯把 foreign key 放在多的那一側
  • FOREIGN KEY 保證 child row 指向存在的 parent row
  • INNER JOIN 只保留兩邊都有符合資料的 row
  • LEFT JOIN 會保留左邊 table，即使右邊沒有符合資料
  • ON DELETE 決定刪除 parent row 時如何處理 child row
  • PostgreSQL 不會自動替 foreign key column 建立 index，需要依查詢方式自行評估

下一篇可以接著介紹 transaction，觀察多個 SQL statement 如何一起成功或一起失敗

參考資料