外鍵約束下的資料刪除三大策略：觸發器逐層刪除、級聯刪除、暫時解除約束

外鍵約束下的資料刪除三大策略：觸發器逐層刪除、級聯刪除、暫時解除約束

外鍵確保參考完整性與一致性，但也使刪除操作受限。以「牛腩新聞發布系統」三層架構（類別 → 新聞 → 評論）為例，詳解三種刪除策略的實作、優缺點與適用場景。

一、為什麼需要外鍵？

• 完整性：防止孤兒資料（如新聞指向不存在的類別）。
• 一致性：事務層面保證相關資料同步變更。

缺乏外鍵會導致資料冗余、查詢結果不可信、修復成本極高。

二、三層架構與刪除方向

類別（表層） → 新聞（中層） → 評論（底層）
刪除表層無影響；刪除中/底層需處理上層依賴。

三、策略一：INSTEAD OF 觸發器逐層刪除（推薦）

在底層表建立 INSTEAD OF DELETE 觸發器，攔截刪除請求，按依賴順序級聯清理：

ALTER TRIGGER trigCategoryDelete ON Category
INSTEAD OF DELETE
AS
BEGIN
  DECLARE @caId INT;
  SELECT @caId = id FROM deleted;

  -- 1. 刪除評論（底層）
  DELETE FROM Comment
  WHERE newsId IN (SELECT newsId FROM News WHERE caId = @caId);

  -- 2. 刪除新聞（中層）
  DELETE FROM News WHERE caId = @caId;

  -- 3. 刪除類別（表層）
  DELETE FROM Category WHERE id = @caId;
END

優點：保留外鍵約束、邏輯集中資料庫層、應用程式無感知、可加入稽核日誌。
缺點：觸發器隱性邏輯較難除錯、跨資料庫遷移需重寫。

四、策略二：外鍵定義 ON DELETE CASCADE 級聯刪除

建表時宣告級聯：

CREATE TABLE Category (
  id VARCHAR(20) PRIMARY KEY,
  name VARCHAR(20) NOT NULL
);
CREATE TABLE News (
  id INT IDENTITY(1,1) PRIMARY KEY,
  title VARCHAR(50) NOT NULL,
  caId VARCHAR(20),
  FOREIGN KEY (caId) REFERENCES Category(id) ON DELETE CASCADE
);

刪除類別時，關聯新聞自動刪除；若新聞再有評論外鍵同樣定義 CASCADE，則連帶刪除。

優點：語法簡潔、資料庫內核執行效率高、無觸發器額外開銷。
缺點：連根拔起，誤刪風險極高（如刪除類別連帶刪除萬筆新聞），無法攔截、無法記錄細節、部分資料庫不支援多層級聯或有深度限制。

五、策略三：暫時解除外鍵約束（脫褲子放屁，慎用）

1. ALTER TABLE News DROP CONSTRAINT fk_news_category;
2. 刪除目標資料。
3. ALTER TABLE News ADD CONSTRAINT fk_news_category FOREIGN KEY (caId) REFERENCES Category(id);

適用場景：一次性資料修正、大量歷史資料清理、約束定義錯誤需重建。
風險：解除期間資料完整性無保障、併發環境極易產生髒資料、大表鎖定時間長。

六、策略對照與選型建議

七、延伸：INSERT/UPDATE/SELECT 是否受外鍵約束？

• INSERT：受約束，外鍵值必須存在於主表。
• UPDATE：受約束，修改外鍵值時新值必須存在；修改主鍵值時若有子表引用會被阻擋（除非 CASCADE UPDATE）。
• SELECT：不受約束影響。

實務建議：**核心業務優先策略一（觸發器）**，兼顧完整性與可控性；輔助表或資料倉儲可用策略二；策略三僅限 DBA 維護窗口使用並全程備份。