刪除和修改資料時考慮外鍵

好啦，我們已經知道什麼是外鍵（FOREIGN KEY），它們怎麼運作，甚至也練習過怎麼用外鍵來建立資料表。但如果現在要刪除資料或改動關聯的紀錄怎麼辦？這裡我們就來聊聊，刪除和修改資料時外鍵會怎麼影響，還有一些小技巧。

想像一下，資料庫就像一個很複雜的紙牌屋。如果你抽掉一張牌，整個結構可能就垮了。這時外鍵就派上用場了，它們可以防止你在刪除關聯資料時「毀掉」資料庫。來看看這到底怎麼運作。

如果試著刪除資料會發生什麼？

當資料表裡有外鍵時，DBMS 會檢查這筆資料有沒有跟其他表有關聯。如果有，直接刪除可能會出現資料完整性錯誤。為了避免這種驚喜，你可以事先設定外鍵的行為，這就是 ON DELETE 的用途。

用 ON DELETE 設定行為

你可以在建立外鍵時，設定以下其中一種規則：

1. ON DELETE CASCADE：刪除父表的紀錄時，所有相關的子表紀錄也會自動被刪掉。
2. ON DELETE SET NULL：不會刪除子表紀錄，但外鍵欄位會設成 NULL。
3. ON DELETE SET DEFAULT：外鍵欄位會設成預設值。
4. ON DELETE RESTRICT（預設行為）：如果有關聯紀錄，不能刪除，會丟出錯誤。
5. ON DELETE NO ACTION：跟 RESTRICT 差不多，但完整性檢查會延到交易結束時才做。

範例：連鎖刪除 ON DELETE CASCADE

-- 客戶資料表
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 訂單資料表
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');

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

-- 檢查 orders 資料表發生了什麼
SELECT * FROM orders; -- 沒有任何紀錄，都被連鎖刪除了！

當我們從 customers 資料表刪除一筆紀錄時，PostgreSQL 會自動把這個客戶的所有訂單從 orders 資料表刪掉。

範例：把關聯設成 NULL ON DELETE SET NULL

-- 訂單資料表，這次用新的行為
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- 插入資料
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

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

-- 檢查 orders_with_null 資料表
SELECT * FROM orders_with_null;

結果：

用 ON DELETE SET NULL，我們可以保留訂單，但把它們「解綁」跟已經不存在的客戶。

修改資料時考慮外鍵

除了刪除，修改父表的資料也會影響關聯紀錄。比如說，如果客戶更改了 customer_id 會怎樣？這時就要用 ON UPDATE 這個選項了。

用 ON UPDATE 設定行為

你可以用以下策略來處理父表資料的變動：

1. ON UPDATE CASCADE：父表外鍵值改變時，所有子表的對應值也會自動更新。
2. ON UPDATE SET NULL：子表的外鍵值會設成 NULL。
3. ON UPDATE SET DEFAULT：設成預設值。
4. ON UPDATE RESTRICT：如果有關聯紀錄，不能改外鍵值。
5. ON UPDATE NO ACTION：檢查會延到交易結束時才做。

範例：連鎖更新 ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- 插入資料
INSERT INTO customers_with_cascade (name) VALUES ('伊凡 伊凡諾夫');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- 修改 customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- 檢查 orders_with_cascade 資料表
SELECT * FROM orders_with_cascade;

結果：

當 customer_id 被改變時，PostgreSQL 會自動在 orders_with_cascade 資料表裡同步更新。

實作練習：enrollments 資料表

來回顧一下我們之前的學生 students 跟課程 courses 的例子。我們會用課程報名資料表 enrollments 來練習怎麼管理刪除資料。

-- 學生資料表
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 課程資料表
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- 中介資料表
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- 插入資料
INSERT INTO students (name) VALUES ('阿列克謝·彼得羅夫');
INSERT INTO courses (title) VALUES ('數學');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- 刪除學生
DELETE FROM students WHERE student_id = 1;

-- 檢查 enrollments 資料表
SELECT * FROM enrollments; -- 空的！紀錄已經自動刪除了。

常見錯誤與避免方法

很常見的錯誤是，刪除父表紀錄時沒設定正確的外鍵行為。比如你沒指定 ON DELETE，預設就是 RESTRICT，這樣會直接報錯。

還有要注意，太常用連鎖操作（CASCADE）有時會造成意外後果。比如你可能會不小心刪掉比預期更多的資料。

為了避免這些問題，建議你：

• 每次都要根據應用邏輯，仔細思考 ON DELETE 跟 ON UPDATE 的行為。
• 重要操作前，先加上查詢來檢查資料再動手刪或改。
• 用 transaction，這樣出錯時可以 rollback。