使用 ON DELETE CASCADE 跟 ON UPDATE RESTRICT

想像一下，你做了一個資料庫，裡面有個客戶表格（customers），還有跟他們有關的訂單（orders）。但有一天你遇到一個問題：如果有個客戶從 customers 表被刪掉了怎麼辦？那這個客戶的訂單要不要一起刪掉？還是讓它們變成「孤兒」，指向一個不存在的客戶？還有，如果你想改客戶的 ID 呢？這時候就輪到 cascade 操作（CASCADE）跟限制（RESTRICT）來幫你控制資料庫的行為啦。

ON DELETE CASCADE 就是一個機制，當你從父表刪掉一筆資料時，會自動把相關的資料也一起刪掉。換句話說，如果你刪掉一個客戶，所有跟他有關的訂單也會跟著消失。

它的運作方式是這樣的。當你在 foreign key 的定義裡加上 ON DELETE CASCADE，資料庫就會「懂」這個關聯資料要自動被刪掉。

範例

假設我們有兩個表格：customers 跟 orders。一個客戶 customers 可以有多個訂單 orders，這就是 ONE-TO-MANY 的關係。我們想要當客戶被刪掉時，他的所有訂單也跟著刪掉。

-- 建立客戶表格
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 建立訂單表格，foreign key 連到客戶表格
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

插入資料到表格

-- 插入資料到客戶表格
INSERT INTO customers (name) VALUES ('伊凡'), ('安娜');

-- 插入資料到訂單表格
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-10-01'),
(1, '2023-10-02'),
(2, '2023-10-03');

orders 表格：

刪除客戶，看看會發生什麼事

-- 刪除 ID 為 1 的客戶
DELETE FROM customers WHERE customer_id = 1;

-- 查查訂單表格還剩下什麼
SELECT * FROM orders;

你看，跟被刪掉客戶有關的訂單也一起被刪掉了。

限制修改：ON UPDATE RESTRICT

ON UPDATE RESTRICT 可以防止你去改父表的值，如果子表還有資料指向那個值的話。這就像一個「防護牆」，不讓你亂改，避免資料關聯被破壞。

怎麼運作？當你加上 ON UPDATE RESTRICT，資料庫就不會讓你去改父表的 key，只要子表還有資料指向它。

範例

我們還是用 customers 跟 orders，但這次在 foreign key 上加上 update 限制。

-- 重新建立訂單表格，加上 update 限制
DROP TABLE orders;

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

試著改一下客戶的 ID

-- 嘗試把 ID 2 改成 5
UPDATE customers 
SET customer_id = 5 
WHERE customer_id = 2;

結果：

ERROR:  update or delete on table "customers" violates foreign key constraint
DETAIL:  Key (customer_id)=(2) is still referenced from table "orders".

你看，資料庫直接丟錯誤，因為這樣會破壞表格之間的關聯。

更多關於 UPDATE 跟它的細節，我下個 level 再跟你說 :P

同時用 ON DELETE CASCADE 跟 ON UPDATE RESTRICT

當然啦，cascade 操作（CASCADE）跟限制（RESTRICT）可以一起用。比如說，你可以設定當父表被刪掉時自動刪除相關資料（ON DELETE CASCADE），但又禁止改它的 ID（ON UPDATE RESTRICT），這樣就不會有奇怪的後果。

範例

我們再來一次，這次訂單表格同時用兩種機制：

DROP TABLE orders;

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

現在：

• 如果你刪掉客戶，他的所有訂單都會被刪掉。
• 如果你想改客戶的 ID，會直接被擋下來。

為什麼這在實際專案很重要？

用 CASCADE 跟 RESTRICT 在大型系統、很多表格有關聯的時候超重要。舉個例子：

在網路商店裡，一個客戶可能有很多訂單。如果客戶決定刪掉自己的帳號，你一定不想讓資料庫裡留下一堆沒人指的訂單。這時候 ON DELETE CASCADE 就超好用。

同時，你可能也想防止有人不小心改了 unique key，這樣才不會把表格之間的關聯弄壞。這時候 ON UPDATE RESTRICT 就派上用場啦。