三大完整性層次
| 層次 | 目標 | 核心機制 | 典型例子 |
|---|
| 實體完整性 | 每列唯一可識別 | 主鍵、唯一索引、UNIQUE 約束、IDENTITY | 身分證字號、員工編號、訂單號 |
| 域完整性 | 欄位值合法、符合業務規則 | 資料型別、CHECK 約束、DEFAULT、NOT NULL、規則、使用者定義型別 (UDT) | 性別 ∈ {M,F}、年齡 0~120、Email 格式、金額 ≥ 0 |
| 參考完整性 | 跨表關聯一致、孤兒列不存在 | 外來鍵 (FK)、觸發器 (複雜規則) | 訂單.CustomerID → 客戶.CustomerID、明細.OrderID → 訂單.OrderID |
實體完整性：主鍵設計黃金法則
- 窄：整數
INT/BIGINT 優於 CHAR/GUID，索引小、聯結快、分頁密集。
- 穩定：不可更新、不包含業務意義 (避免業務變更導致 PK 變更)。
- 單調遞增：
IDENTITY(1,1) / SEQUENCE 減少頁面分裂、碎片。避免 GUID (NEWID()) 做叢集索引** (隨機 I/O、碎片爆炸)，若必須用 GUID 改用 NEWSEQUENTIALID() + 非叢集索引。
- 單欄優先：複合主鍵增加所有 FK 寬度、聯結複雜度，僅在純關聯表 (多對多中介表) 考慮。
域完整性：約束優於觸發器優於應用層
CREATE TABLE Employees (
EmpID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL CONSTRAINT CK_Employees_Gender CHECK (Gender IN ('M','F')),
BirthDate DATE NOT NULL CONSTRAINT CK_Employees_Age CHECK (BirthDate >= '1900-01-01' AND BirthDate <= DATEADD(DAY, -18*365, GETDATE())),
Email NVARCHAR(100) CONSTRAINT CK_Employees_Email CHECK (Email LIKE '%_@_%._%'),
Salary MONEY NOT NULL CONSTRAINT CK_Employees_Salary CHECK (Salary >= 0),
DeptID INT NOT NULL CONSTRAINT DF_Employees_DeptID DEFAULT (1),
RowVer ROWVERSION NOT NULL -- 樂觀並發
);
- CHECK 約束：資料庫引擎強制、效能最佳、無法繞過 (含大量匯入需
CHECK_CONSTRAINTS 選項)。
- DEFAULT 約束**：減少應用層邏輯、確保一致性。
- UDT (User-Defined Type)**：跨表重用域定義 (如
CREATE TYPE Email FROM NVARCHAR(100) NOT NULL + 規則/約束綁定)。
參考完整性：外來鍵完整部署指南
-- 父表
CREATE TABLE Customers (
CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100) NOT NULL
);
-- 子表：外來鍵 + 級聯動作
CREATE TABLE Orders (
OrderID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL DEFAULT (GETDATE()),
CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
ON UPDATE CASCADE -- 父鍵變更同步更新子表 (PK 穩定原則下極少用)
ON DELETE NO ACTION -- 禁止刪除有訂單的客戶 (業務規則)
);
-- 多對多中介表
CREATE TABLE OrderDetails (
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Qty INT NOT NULL CHECK (Qty > 0),
CONSTRAINT PK_OrderDetails PRIMARY KEY (OrderID, ProductID),
CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY (OrderID) REFERENCES Orders (OrderID) ON DELETE CASCADE,
CONSTRAINT FK_OrderDetails_Products FOREIGN KEY (ProductID) REFERENCES Products (ProductID) ON DELETE NO ACTION
);
外來鍵關鍵選項
| 選項 | 行為 | 建議 |
|---|
ON DELETE CASCADE | 刪除父列 → 自動刪除子列 | 中介表、明細表、臨時資料；核心主體表 **嚴禁** (防誤刪連鎖)。 |
ON DELETE SET NULL | 刪除父列 → 子列 FK 設 NULL | FK 允許 NULL 時可用 (如員工離職、專案歸檔)。 |
ON DELETE NO ACTION (預設) | 有子列時拒絕刪除父列 | **預設最安全**，強制業務先處理子資料 (轉移/歸檔/刪除)。 |
ON UPDATE CASCADE | 更新父鍵 → 同步子鍵 | PK 應穩定不變，**極少啟用**；若啟用需確保索引重建效能。 |
NOT FOR REPLICATION | 複寫代理執行時不檢查 FK | 合併/交易複寫提升效能，訂閱端自行維護一致性。 |
觸發器輔助複雜參考完整性
FK 無法表達：條件式關聯、跨資料庫、自訂錯誤訊息、複雜業務規則 (如「刪除客戶前必須確認無未出貨訂單、且帳齡 > 90 天」)。
CREATE TRIGGER TR_Customers_PreventDelete
ON Customers
INSTEAD OF DELETE
AS
BEGIN
IF EXISTS (
SELECT 1 FROM deleted d
JOIN Orders o ON d.CustomerID = o.CustomerID
WHERE o.ShippedDate IS NULL OR DATEDIFF(DAY, o.OrderDate, GETDATE()) <= 90
)
BEGIN
RAISERROR(N'客戶有未出貨或帳齡 ≤ 90 天訂單，無法刪除。', 16, 1);
ROLLBACK;
RETURN;
END
DELETE FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM deleted);
END
效能與維運考量
- FK 索引**：子表 FK 欄位**必須建立索引** (非叢集)，否則父表刪除/更新需掃描子表全表 (共享鎖升級、封鎖)。
- 信任與非信任 FK**：
ALTER TABLE ... WITH CHECK CHECK CONSTRAINT 重新驗證現有資料；WITH NOCHECK 僅限未來資料，**查詢最佳化器不信任非信任 FK**，無法利用連結消除/簡化計畫。
- 大量匯入**：
BULK INSERT / bcp / SSIS 加上 CHECK_CONSTRAINTS / FIRE_TRIGGERS 選項，否則略過 FK/觸發器/CHECK。
- 定期稽核**：
DBCC CHECKCONSTRAINTS 檢測違規列 (極罕見，通常因 NOCHECK 或大量匯入導致)。
總結：完整性部署清單
[ ] 所有表有窄、穩定、單調遞增單欄主鍵 (PK)
[ ] 所有業務欄位有適當 CHECK 約束 (型別、範圍、格式、非空)
[ ] 所有父子關聯有外來鍵 (FK) + 子表 FK 索引
[ ] FK 級聯動作依業務語義設定 (預設 NO ACTION，明細表 CASCADE)
[ ] 複雜規則用 INSTEAD OF 觸發器，並文件化
[ ] 所有約束為 TRUSTED 狀態 (sys.foreign_keys.is_not_trusted = 0)
[ ] 大量匯入流程包含 CHECK_CONSTRAINTS / FIRE_TRIGGERS
[ ] 定期 DBCC CHECKCONSTRAINTS + 索引碎片整理
