資料完整性檢查

今天我們要開始搞懂 foreign key 怎麼幫我們顧好資料完整性，還有怎麼避免常見的資料不一致或錯誤的問題。

首先來說說，什麼是「資料完整性」。想像一下，你有一個訂單表（orders）跟一個客戶表（customers）。如果有個訂單的客戶在客戶表裡根本不存在，這就破壞了完整性。很重要的一點是，所有有關聯的表格資料都要邏輯上一致才行。

資料完整性代表：

• 沒有「空」的參照：如果我們在另一個表格參照了什麼，那個「什麼」一定要存在。
• 對修改錯誤有抵抗力：如果我們從表格刪掉一個被其他紀錄參照的值，資料庫應該要提醒我們，或是正確處理這個狀況。

這就是為什麼 PostgreSQL 要用 foreign key。

foreign key 怎麼確保資料完整性？

當你在表格裡建立 foreign key，PostgreSQL 會自動檢查：

1. 父表裡有沒有這筆資料。 在插入或更新紀錄之前，PostgreSQL 會檢查你指定的 foreign key 在關聯表裡是不是存在。
2. 刪除或修改資料。 在父表刪除或更新紀錄之前，PostgreSQL 會檢查有沒有子表的紀錄參照到它。

foreign key 就像個「守門員」。它不會讓錯誤的資料進來，也保證表格之間的互動都在規則內。

例子：學生和課程表的資料完整性

假設我們有兩個表格 — students 跟 courses。每個學生可以選多個課程。為了反映這個關係，我們會用一個 enrollments 表。來看一下，如果有人想讓學生選一個不存在的課程會發生什麼。

步驟 1. 建立三個有關聯的表：

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 (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id)
);

這裡：

• 在 enrollments 表裡，我們明確指定了 foreign key student_id 跟 course_id，它們分別參照 students 跟 courses 的 primary key。

常見的資料完整性檢查

1. 插入資料時的檢查

如果我們試著在 enrollments 表插入一筆不存在的 student_id 或 course_id，就會出錯。

例子：

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- 錯誤！ID 999 的學生不存在。

錯誤訊息：

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. 刪除資料時的檢查

來試試看從父表刪掉一筆被參照的紀錄。

例子：

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO courses (title) VALUES ('Mathematics');

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- 成功插入

DELETE FROM students WHERE student_id = 1; -- 錯誤，因為這個學生還有選課！

錯誤訊息：

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

要正確刪除這種紀錄，我們會用 CASCADE、SET NULL 或 RESTRICT 這些策略，之前有聊過。

用 foreign key 檢查完整性的例子

例子 1：自動防止錯誤資料

靠著 foreign key，PostgreSQL 會自動擋掉「不存在」的資料：

-- 試著把不存在的學生加到課程裡：
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- 錯誤！ID 42 的學生不存在。

這樣就能保證，學生如果沒在 students 表裡，是不能選課的。

例子 2：用 ON DELETE CASCADE 刪資料

如果 foreign key 設成 ON DELETE CASCADE，那你在父表刪掉紀錄時，子表的相關資料也會一起被刪掉。

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- 先移除舊的 foreign key

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- 這時 enrollments 表裡的紀錄也會一起刪掉

例子 3：用 ON UPDATE 處理變更

如果 foreign key 設成 ON UPDATE CASCADE，那父表的值改變時，PostgreSQL 會自動幫你更新子表的資料。

-- 設定 foreign key，讓父表 key 改變時子表自動跟著改：
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- 改變學生的 ID：
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- 這時 enrollments 表裡的 student_id 也會自動變成 10。

資料完整性測試

測試 foreign key 設定在不同情境下的表現很重要：

1. 試著插入錯誤的 student_id 或 course_id。
2. 刪掉 students 裡的資料，看看 enrollments 表會怎樣。
3. 改變 students 表裡的資料，確認相關紀錄有沒有跟著更新。

使用 foreign key 的注意事項

有時候會遇到一些讓人困惑的狀況：

• 沒有 index。 如果父表（像 students）沒有被參照欄位的 index，PostgreSQL 可能會「努力」但速度變慢。所以父表的 primary key 一定要有 index。
• 循環參照。 如果兩個表互相參照，插入資料時會比較麻煩。這種情況設計時要特別小心。
• 刪除所有資料。 如果你要用 cascade 刪掉所有資料，要注意子表的資料型態，避免出現意外的行為。

為了避免這些問題，設計表格時要想清楚，還有在正式用之前多測試關聯規則。