萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

淺談 SQL Server 2005 參考完整性:新增 CASCADE/SET NULL/SET DEFAULT 規則詳解

為什麼要重新認識參考完整性?

  • SQL Server 2005 新增 SET NULL 與 SET DEFAULT 兩種參考動作,鮮為人知。
  • 初學者最難掌握的核心概念之一,實務卻極常用。

參考完整性基礎觀念

透過 主鍵 (PK) 與 外鍵 (FK) 約束,保證主表與從表資料一致性。強制執行時防止:

  1. 從表新增/修改為主表不存在的鍵值。
  2. 主表修改鍵值導致從表產生孤兒記錄。
  3. 主表刪除仍有從表關聯記錄存在。

SQL Server 2005 新增的刪除/更新規則

於從表外鍵屬性 → 刪除規則 / 更新規則 中設定:

規則行為前置條件備註
無動作 (No Action)報錯回滾,禁止操作無預設值
級聯同步刪除/更新所有關聯從表列無2000 稱「級聯」,2005 稱「級聯」
設為 NULL將從表外鍵欄位設為 NULL外鍵欄位 允許 NULL2005 新增
設為預設值將從表外鍵欄位設為定義的 DEFAULT 值外鍵欄位 有 DEFAULT 約束2005 新增

實作示範:學生-成績表

-- 建立學生表 (主表)
CREATE TABLE Students (
    SCode INT PRIMARY KEY,
    SName NVARCHAR(20)
);

-- 建立成績表 (從表),外鍵設定 CASCADE DELETE
CREATE TABLE Scores (
    ScoreID INT PRIMARY KEY,
    StudentID INT,
    Subject NVARCHAR(20),
    Score INT,
    CONSTRAINT FK_Scores_Students
        FOREIGN KEY (StudentID) REFERENCES Students(SCode)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

刪除學生時,對應成績自動刪除;修改學號時,成績表同步更新。

SET NULL 實務場景:員工離職保留紀錄

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Employees
    FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)
    ON DELETE SET NULL;  -- 員工離職,訂單保留但歸屬清空

前提:Orders.EmpID 允許 NULL。

SET DEFAULT 實務場景:歸屬預設歸屬

ALTER TABLE Documents
ADD CONSTRAINT FK_Documents_Departments
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
    ON DELETE SET DEFAULT;  -- 部門裁撤,文件歸屬預設為「總務處」

前提:Documents.DeptID 有 DEFAULT (999) 約束。

SSMS 圖形化介面操作路徑

  1. 物件總管 → 從表 → 右鍵「修改」
  2. 表設計師 → 右鍵「關聯性」
  3. 選取關聯 → 下方「INSERT 和 UPDATE 規格」展開
  4. 設定「刪除規則」與「更新規則」

與 SQL Server 2000 差異對照

2000 僅支援 無動作 與 級聯。若需 SET NULL/SET DEFAULT,2000 必須撰寫觸發器實現(如 titles 刪除觸發 titleauthor/sales/roysched 級聯刪除)。

觸發器級聯操作補充說明

觸發器為自動執行,可偵測 DML 操作並級聯影響相關表。範例:titles.title_id 刪除觸發器,以 title_id 為鍵定位並刪除 titleauthor、sales、roysched 對應列。

結語

善用 CASCADE/SET NULL/SET DEFAULT 讓資料庫自動維護參考完整性,減少應用層程式碼、避免人為疏漏。設計時務必依業務語意選擇正確規則:核心主檔刪除通常級聯;歷史紀錄保留用 SET NULL;分類歸屬變更用 SET DEFAULT。

關鍵字: SQL Server 2005、參考完整性、外鍵、CASCADE、SET NULL、SET DEFAULT、級聯、觸發器、SSMS

copyright © 萬盛學電腦網 all rights reserved