資料庫邏輯設計中,主鍵策略直接影響系統效能、擴展性與維護成本。本文深入剖析傳統「業務主鍵/聯合主鍵」在主從表架構下的弊端,提出採用「非業務代理鍵」搭配自訂序列機制的最佳實踐方案,並提供完整 T-SQL 實作碼。
在傳統設計中,主鍵常直接源於業務邏輯(如銷售單號、商品編碼),隨著主從關係層級加深(主表 → 明細表 → 子明細表),會衍生出嚴重問題:
核心原則:每張表(含主表、從表)均新增一個無業務意義的數值型欄位(如 `U_ID BIGINT`)作為主鍵(PK)。子表透過儲存父表的 `U_ID` 作為外鍵(FK)建立關聯。
雖然 SQL Server 原生 `IDENTITY` 簡單易用,但存在硬傷:
建立序列管理表 `T_DB_SEQ`,模擬 Oracle Sequence 功能:
CREATE TABLE T_DB_SEQ (
SEQ_NAME VARCHAR(50) NOT NULL, -- 序列名稱
SEQ_OWNER VARCHAR(50) NOT NULL DEFAULT 'DBO', -- 擁有者
SEQ_CURRENT BIGINT NOT NULL DEFAULT 0, -- 目前值
SEQ_MIN BIGINT NOT NULL DEFAULT 0, -- 最小值
SEQ_MAX BIGINT NOT NULL DEFAULT 0, -- 最大值 (原文有誤重複定義 SEQ_MIN,此處修正)
SEQ_STEP INT NOT NULL DEFAULT 1, -- 遞增步長
IF_CYCLE INT NOT NULL DEFAULT 0, -- 是否循環 (0=否, 1=是)
CONSTRAINT PK_T_DB_SEQ PRIMARY KEY CLUSTERED (SEQ_NAME, SEQ_OWNER)
);
配合三大核心函數(需自行實作邏輯,建議用預存程序 + 交易鎖定保證並發安全):
F_Create_SEQ(@SeqName):建立序列記錄。F_Get_Next_Seq_Val(@SeqName):取得下一值(核心,需處理並發更新 `SEQ_CURRENT += SEQ_STEP`)。F_Get_Seq_Cur_Val(@SeqName):取得目前值。輔助函數:`F_Set_Seq_Val`、`F_Del_Seq`、`F_Seq_Exists`。
CREATE TABLE T_PK_DEMO (
U_ID BIGINT NOT NULL, -- 代理主鍵 (由序列產生)
COL_OTHER VARCHAR(20) NOT NULL,
CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED (U_ID)
);
CREATE TABLE T_PK_DEMO_C (
U_ID BIGINT NOT NULL, -- 子表自身代理主鍵
COL_OTHER VARCHAR(20) NOT NULL,
P_ID BIGINT NOT NULL, -- 父表 U_ID (外鍵)
CONSTRAINT PK_T_PK_DEMO_C PRIMARY KEY NONCLUSTERED (U_ID),
CONSTRAINT FK_T_PK_DEMO_C FOREIGN KEY (P_ID)
REFERENCES T_PK_DEMO(U_ID) ON DELETE CASCADE
);
改用代理鍵後,原業務鍵(如帳號、身分證字號)失去 PK 唯一約束,必須額外處理:
ALTER TABLE T_PK_DEMO ADD CONSTRAINT UQ_Col_Other UNIQUE NONCLUSTERED (COL_OTHER);代理鍵(`U_ID`)特性:唯一、極少更新、無業務查詢意義。
聚集索引(Clustered Index)資源稀缺(每表僅一個),應留給高頻範圍查詢、排序、JOIN 的業務欄位(如 `CreateDate`、`CategoryID`)。
因此,代理鍵主鍵預設建立 NONCLUSTERED(非叢集)索引,避免佔用寶貴的叢集索引位置。
採用單一整數代理鍵後,分頁查詢變得極其高效:
-- 範例:取得第 2 頁,每頁 50 筆,依建立時間降冪
SELECT * FROM T_PK_DEMO
ORDER BY CreateDate DESC
OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;
搭配存儲程序在 DB 端分頁,避免大量資料傳輸至 Web 伺服器,大幅降低網路頻寬與記憶體壓力。
「代理鍵 + 自訂序列」模式雖增加初期建置成本(序列管理表、函數開發),但從長遠看,它解耦了業務邏輯與實體儲存,消除了聯合主鍵的連鎖副作用,是大型、高併發、長生命週期企業級 SQL Server 應用的架構標準配置。