模擬 ONE-TO-MANY 關聯在資料表之間

現在是時候升級一下我們的技能，來建立在關聯式資料庫裡最常見的關聯之一——ONE-TO-MANY 關聯。

想像一下有個小公司。一個員工只能在一個部門工作，但一個部門可以有很多員工。我們有兩個現實世界的物件：員工和部門。它們之間的關係可以描述成「一個部門可以包含很多員工」，或者正式一點「一對多」（ONE-TO-MANY）。

同樣的 ONE-TO-MANY 關係到處都是：

• 一個客戶可以下很多訂單；
• 一個作者可以寫很多本書；
• 一個老師可以教很多學生。

在關聯式資料庫裡，ONE-TO-MANY 關聯是透過外鍵（FOREIGN KEY）來實現的。「多」的那張表（MANY）裡有一個欄位指向「一」的那張表（ONE）的主鍵。

怎麼建立 ONE-TO-MANY 關聯

我們來看個經典例子：客戶和訂單的關聯。一個客戶可以下很多訂單，但每個訂單只屬於一個客戶。我們會建立兩張表：customers（客戶）和 orders（訂單）。

資料表 customers

這是我們的「一」的表。它會存客戶的資訊。

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- 客戶唯一識別碼
    name TEXT NOT NULL              -- 客戶名稱
);

資料表 orders

這是「多」的表。它存訂單，每個訂單都有個外鍵 customer_id，指向 customers 表裡的 customer_id。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- 訂單唯一識別碼
    order_date DATE NOT NULL,              -- 訂單日期
    customer_id INT REFERENCES customers(customer_id) -- 外鍵
);

實際應用

插入資料

現在我們來加點資料到表裡，測試一下關聯有沒有用。

先加客戶到 customers 表：

INSERT INTO customers (name)
VALUES
    ('Ada Lovelace'),
    ('Grace Hopper'),
    ('Linus Torvalds');

結果：

再加訂單到 orders 表：

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Ada 的訂單
    ('2023-10-02', 2),  -- Grace 的訂單
    ('2023-10-03', 1);  -- Ada 的另一個訂單

orders 表：

注意：新增訂單時一定要填現有的 customer_id。如果你亂填一個不存在的 ID，資料庫會直接報錯。這就是資料完整性的保護啦。

檢查關聯

現在來看看我們的表怎麼連在一起。比如說：Ada Lovelace 下了哪些訂單？

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'Ada Lovelace';

結果：

這裡我們用 JOIN 指令，把兩張表用外鍵連起來。超方便又漂亮——而且資料不會重複！

為什麼要這樣做

ONE-TO-MANY 關聯在現實生活中超常見又超實用。想像一個網路商店，有成千上萬的客戶和幾百萬筆訂單。如果每筆訂單都重複存客戶資料，資料量會爆炸。我們只要把客戶資訊存在一張表，訂單存在另一張表，資料就超整齊又省空間。

而且，資料能這樣連起來，查詢分析也超強大。比如你可以問：「每個客戶下了幾筆訂單？」或「哪些客戶在上個月有下訂單？」

常見問題和陷阱

新手最常踩雷的地方：

沒加外鍵。 如果你忘了在「多」的表加外鍵，這個關聯就只存在你腦袋裡，資料庫根本不會幫你管。這樣你很容易搞出「壞掉」的資料庫，訂單會指向不存在的客戶。

想刪「一」的表的資料。 比如你把 customers 裡的客戶刪掉，他的訂單在 orders 裡就會變成「孤兒」。為了避免這種情況，可以用 ON DELETE CASCADE，這樣刪客戶時會自動把他的訂單也刪掉。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE
);

現在，如果你刪掉一個客戶：

DELETE FROM customers WHERE customer_id = 1;

他的所有訂單也會一起被刪掉。資料庫乾淨得像剛泡好的咖啡一樣。

插入資料時出錯。 如果你試著插入一筆訂單，customer_id 填了不存在的值，就會看到像這樣的錯誤：

ERROR:  insert or update on table "orders" violates foreign key constraint