SQL Server 主鍵約束衝突導致刪除失敗的解決方案

在資料庫開發中，主鍵與外鍵的關聯約束是保證資料完整性的核心機制。然而，當嘗試刪除主表（父表）中的記錄時，若子表（從表）存在對應的外鍵資料，資料庫會拋出類似「DELETE 語句與 REFERENCE 約束衝突」的錯誤，導致操作失敗。

常見錯誤場景

假設有兩張表：ServerLib（主表，主鍵為 ServerID）與 IPAddress（從表，外鍵 ServerID 關聯主表）。當執行 DELETE FROM ServerLib WHERE ServerID = 1 時，若 IPAddress 表中存在 ServerID = 1 的記錄，SQL Server 將阻止刪除並報錯：

DELETE 語句與 REFERENCE 約束 "FK_ipaddress_serverlib" 沖突。該沖突發生於資料庫 "XiangLongDatabase"，表 "dbo.ipaddress", column 'serverid'。

傳統解決方式及其缺點

許多開發者會採用「先查後刪」的邏輯：

1. 先 SELECT 從表確認是否存在關聯記錄。
2. 若存在，先 DELETE 從表記錄。
3. 再 DELETE 主表記錄。

這種方式會導致資料庫執行多條 SELECT 和 DELETE 語句，增加網路往返與鎖定開銷，且在高併發下易產生競態條件。

更優雅的解決方案

方案一：級聯刪除（ON DELETE CASCADE）

在建立外鍵時指定 ON DELETE CASCADE，資料庫會自動刪除從表中對應的記錄：

ALTER TABLE IPAddress
ADD CONSTRAINT FK_IPAddress_ServerLib
FOREIGN KEY (ServerID) REFERENCES ServerLib(ServerID)
ON DELETE CASCADE;

優點：邏輯簡單，資料庫層面保證原子性。
缺點：需謹慎使用，避免誤刪大量關聯資料；部分業務場景需保留歷史記錄，不適用。

方案二：軟刪除（邏輯刪除）

在主表新增 IsDeleted 或 Status 欄位，不執行物理 DELETE，而是 UPDATE 標記為已刪除。查詢時過濾該標記。從表資料保留，滿足審計與歷史追溯需求。

方案三：存儲過程封裝事務

若必須物理刪除且不想用級聯，可編寫存儲過程，在一個事務中按順序刪除從表再刪主表，減少網路交互並確保一致性：

CREATE PROCEDURE DeleteServerLib @ServerID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    DELETE FROM IPAddress WHERE ServerID = @ServerID;
    DELETE FROM ServerLib WHERE ServerID = @ServerID;
    COMMIT TRANSACTION;
END;

最佳實踐建議

• 優先評估業務是否允許級聯刪除，允許則直接在 DDL 定義 ON DELETE CASCADE。
• 若需保留歷史資料，採用軟刪除模式。
• 必須物理刪除且無級聯時，使用存儲過程或應用層事務保證刪除順序（先從表，後主表）。
• 避免在應用層循環執行多條 SELECT/DELETE，增加資料庫負載與死鎖風險。

理解外鍵約束的本質，選擇適合業務場景的刪除策略，才能兼顧資料完整性與系統效能。