隨著主從關係層層延伸(主表單據號 → 明細表單據號+商品碼 → 子明細單據號+商品碼+價格類別),帶來四大問題:
為每張表新增一個 無業務含義的數值型單欄主鍵(如 BIGINT IDENTITY 或自建序列)。
CREATE TABLE T_PK_DEMO (
U_ID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
COL_OTHER VARCHAR(20) NOT NULL
);
限制:值由系統完全控制、無法手工指定/修改、複寫/還原/資料移轉時麻煩、插入後才能取得值(需 SCOPE_IDENTITY())。
-- 序列管理表
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 9223372036854775807,
SEQ_STEP INT NOT NULL DEFAULT 1,
IF_CYCLE BIT NOT NULL DEFAULT 0,
PRIMARY KEY CLUSTERED (SEQ_NAME, SEQ_OWNER)
);
-- 核心函數(概念)
-- F_CREATE_SEQ(@name) -- 建立序列
-- F_GET_NEXT_SEQ_VAL(@name) -- 取得下一值(含並發控制)
-- F_GET_SEQ_CUR_VAL(@name) -- 取得當前值
-- F_SET_SEQ_VAL(@name, @val) -- 設定值
-- F_DEL_SEQ(@name) -- 刪除序列
應用層先呼叫 F_GET_NEXT_SEQ_VAL 取得主鍵值,再執行 INSERT,完全掌控時機與值。
-- 父表
CREATE TABLE T_PK_DEMO (
U_ID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
COL_OTHER VARCHAR(20) NOT NULL
);
-- 子表:自身用代理鍵,外鍵指向父表代理鍵
CREATE TABLE T_PK_DEMO_C (
U_ID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
COL_OTHER VARCHAR(20) NOT NULL,
P_ID BIGINT NOT NULL,
CONSTRAINT FK_T_PK_DEMO_C FOREIGN KEY (P_ID)
REFERENCES T_PK_DEMO(U_ID) ON DELETE CASCADE
);
代理鍵解決關聯效能,但業務規則(如使用者帳號唯一)仍需強制:
UNIQUE CONSTRAINT 或 UNIQUE NONCLUSTERED INDEX。代理鍵主鍵建議 NONCLUSTERED,將寶貴的叢集索引留給:
OrderDate、CreateTime)單欄代理鍵使得分頁查詢(OFFSET/FETCH 或 TOP + 鍵值定位)極其簡單高效,避免複合鍵分頁的噩夢。配合覆蓋索引,大資料量分頁毫秒級回應。