Allen 技術筆記

2. 主索引鍵

閱讀時間約 17 分鐘

主索引鍵(Primary Key)

簡介 主索引鍵

  1. 主索引鍵的策略性價值

    在現代資料庫架構中，主索引鍵 (Primary Key) 不僅僅是一個技術設定，更是保障資料品質與系統穩定性的核心基石。一個深思熟慮的主索引鍵設計，是高效能、高可靠性系統的起點。本規範旨在為資料庫架構師與開發人員提供一套清晰、可執行的設計指南，以確保系統的資料完整性、效能與長期可維護性。

    主索引鍵的核心概念是為每一筆資料記錄提供一個 「唯一值」。這個唯一識別碼確保了任何資料操作（新增、修改、刪除）都能精準地鎖定目標，從根本上杜絕因條件模糊而導致的資料誤改或誤刪風險。

    主索引鍵的實現方式可歸納為兩大類

    • 使用現有欄位：
      將業務上有意義且保證唯一的欄位（例如：身分證號碼、訂單編號）直接指定為主索引鍵。此類索引鍵被稱為自然鍵 (Natural Key)。
    • 另開新欄位：
      建立一個沒有任何業務意義、僅用於唯一識別資料記錄的欄位。此類索引鍵被稱為代理鍵 (Surrogate Key)。

  2. 確保資料唯一性的挑戰與解決方案
    本章節將透過一個實際案例，逐步揭示在資料操作中依賴非唯一性欄位所帶來的潛在風險，並展示一個穩健的主索引鍵設計如何從根本上解決這些問題，確保每一次操作都精準無誤。

    • 第一階段：使用非唯一欄位（學生姓名）

      假設我們有一張「學生資料表」，現在需要執行一個指令：「修改學生姓名為 王大明 的同學成績為 90 分」。

      由於「王大明」這個姓名在資料表中存在重複（同名同姓），這個看似簡單的操作將會導致系統無法區分目標，從而錯誤地將學生編號為 A001 和 A003 的兩位同學的成績都修改為 90。這種操作不僅違反了業務邏輯，更直接破壞了資料的準確性與完整性。

    • 第二階段：使用業務唯一欄位（學生編號）

      為了解決上述問題，我們可以選擇一個在業務上具有唯一性的欄位，例如「學生編號」，來作為操作的條件。現在，指令變為：「修改學生編號為 A001 的同學成績為 90分」。

      由於「學生編號」在設計上是唯一的，系統可以精準地鎖定 A001 這筆記錄並更新其成績，而不會影響到其他任何資料。這成功解決了第一階段的模糊性問題。

      然而，使用業務欄位（自然鍵）仍有隱憂：如果未來業務規則改變（例如，學生編號規則需要重編），主索引鍵將被迫修改，這會對所有關聯的資料表產生連鎖反應，維護成本極高。

  3. 第三階段：使用代理鍵（id）

    最穩固的策略是引入一個與業務邏輯完全無關的代理鍵，例如一個自動增長的整數 id。此時，指令更新為：「修改 id 為 1 的同學成績為 90分」。

    這個 id 欄位不承載任何業務意義，它的唯一使命就是作為資料的內部識別碼。因此，無論學生的姓名、學號如何變更，id 始終保持不變。這種設計實現了業務邏輯與資料實體標識的徹底解耦，提供了最高級別的穩定性與可靠性。

    綜上所述，另開新欄位作為代理鍵是業界公認的最佳實踐。接下來，我們將深入探討代理鍵的兩種主流實現方式。

主索引鍵策略選擇框架

在確定採用代理鍵策略後，下一個關鍵決策是在「流水號 (Serial Number)」與「全域唯一識別碼 (GUID)」之間進行選擇。這兩種技術各有優劣，適用於不同的應用場景。本章節將提供一個清晰的決策框架與指導原則。

  1. 核心特性對比

    特性流水號 (Serial Number)全域唯一識別碼 (GUID)
    核心概念一個由資料庫自動遞增的整數，具有天然的順序性。一個 128 位元的數值，透過演算法確保其在全球範圍內的唯一性。
    優點易於理解、可讀性高、佔用儲存空間小、作為叢集索引 (Clustered Index) 時，因其循序性可避免頁面分裂 (Page Split)，寫入效能極佳。全域唯一、適合分散式系統、可在應用程式 端離線生成、資料合併時不會產生衝突。
    缺點在分散式環境中難以生成且易衝突、在多資料庫合併時會產生主鍵衝突。可讀性差、佔用儲存空間較大、作為叢集索引時，因其無序性與隨機性，容易引發頁面分裂，影響寫入效能。

  2. 選擇總體原則
    基於上述特性，主索引鍵的選擇應遵循一個核心原則：
    大部份情形下使用流水號自動編號

    流水號的簡潔、高效與可預測性使其成為絕大多數單體應用或標準業務系統的首選。然而，在某些特定情境下，GUID 的全域唯一性則成為不可或缺的特性。

  3. GUID 的適用情境

    僅在滿足以下特定條件時，才應優先考慮使用 GUID：

    • 當資料「強調前後關係不能變」時：
      記錄之間的邏輯關係（例如父子關係）必須在任何環境下都保持絕對不變。
      即使資料從一個資料庫遷移到另一個資料庫，這種關係也必須被完整保留。
    • 當該值是「一定不能變的值」時：
      記錄的身份標識需要在跨系統、跨資料庫的生命週期中維持絕對的唯一性與一致性。
      例如，一筆簽核記錄在 ERP、CRM 和 HR 系統中流轉時，需要一個共同的、永不改變的識別碼來追蹤它。

    理論框架需要透過實際業務場景來驗證。下一章節將深入分析具體案例，展示如何應用這些原則進行決策。

深度情境分析

本章節旨在將前述的理論框架應用於兩個典型的企業級應用場景：簽核流程 (Workflow) 與物料需求清單 (BOM)，以具體案例來論證不同主索引鍵策略的適用性及其深遠影響。

  1. 情境一：簽核流程 (Workflow) 系統

    一個典型的簽核流程可能包含多個關卡，例如：部門主管 → 管理部 → 財務部 → 總經理。

    在此類系統中，每一份簽核單、每一個流程節點的「身份」都至關重要。這份簽核單的資料可能需要在多個內部系統（如 ERP、HR 系統）之間流轉、同步與追蹤。如果使用流水號作為主索引鍵，當資料從測試環境匯入正式環境，或在不同部門的子系統間同步時，流水號會被重新生成，導致無法追蹤同一個簽核實例。

    在這種情況下，使用 GUID 則能完美解決問題。在簽核單創建之初就生成一個 GUID，這個 ID 將伴隨其整個生命週期。無論它被複製、遷移或同步到任何系統，其唯一標識都穩固不變，完全符合「一定不能變的值」的設計原則，確保了跨系統資料的一致性與可追溯性。

  2. 情境二：物料需求清單 (BOM) 管理

    BOM (Bill of Materials) 描述了一個產品的階層式結構，從最終製成品分解至半成品，再到最底層的原材料。

    在企業資源規劃 (ERP) 系統中，BOM 通常分為兩種類型：

    • EBOM (Engineering BOM)：
      研發階段使用的 BOM，結構隨時可能變更，不直接用於生產。
    • MBOM (Manufacturing BOM)：
      經過驗證的生產用 BOM，結構穩定，不可隨意變更。
      一個關鍵的業務流程是「EBOM 轉 MBOM」，即將研發成熟的物料清單轉移至生產系統中。此時，主索引鍵的選擇將直接決定流程的成敗。

  3. 使用流水號的陷阱
    假設 EBOM 和 MBOM 都使用流水號作為主索引鍵 id，並用 父階編號 來維護層級關係。

    如上表所示，當 EBOM 的資料匯入 MBOM 後，S3 的 父階編號 仍然是 1，但 MBOM 中 id=1 的料品是 P1 而非 P2，導致層級關係完全錯亂。當我們將 EBOM 的資料匯入 MBOM 時，MBOM 資料庫會為這些新記錄重新生成一套流水號 id。這導致原先在 EBOM 中用來定義父子關係的 父階編號 完全失效，整個產品的樹狀結構瞬間崩潰。這是一個災難性的後果。

  4. 使用 GUID 的優勢
    現在，我們改用 GUID 作為主索引鍵 id 和 父階編號。

    由於 GUID 的全域唯一性，當 EBOM 的資料被匯入 MBOM 時，id 和 父階編號 的值被原封不動地保留下來。物料之間的父子關係得到了完美的繼承。這完美詮釋了「強調前後關係不能變」的原則。

    總結來說，根據業務場景的特性——特別是是否涉及跨系統整合或資料遷移後的關係保持——來選擇合適的主索引鍵策略至關重要。

實作技術指南：SQL Server 代理鍵建置

本章節將提供在 Microsoft SQL Server 環境下，三種主流代理鍵的具體實作步驟與設定說明，包含完整的 SQL DDL (Data Definition Language) 腳本，旨在為開發人員提供直接可用的技術參考。

  1. 流水號自動編號 (int IDENTITY)

    此方法是實現自動遞增流水號最常用、最高效的方式，透過 int 資料類型搭配 IDENTITY 屬性來實現。

    CREATE TABLE [dbo].[PrimaryKey1](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserNo] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
CONSTRAINT [PK_PrimaryKey1] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

  2. GUID 原生物件型別 (uniqueidentifier)

    此方法使用 SQL Server 原生的 uniqueidentifier 資料類型來儲存 GUID，並搭配 newid() 函數在新增記錄時生成預設值。

    CREATE TABLE [dbo].[PrimaryKey2](
	[Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[UserNo] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
CONSTRAINT [PK_PrimaryKey2] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[PrimaryKey2] ADD  CONSTRAINT    [DF_PrimaryKey2_Id]  DEFAULT (newid()) FOR [Id]
GO

  3. GUID 文字型別 (nvarchar)

    這是一種替代方案，使用 nvarchar 欄位來儲存 GUID 的文字表示法。

    CREATE TABLE [dbo].[PrimaryKey3](
	[Id] [nvarchar](50) NOT NULL,
	[UserNo] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
CONSTRAINT [PK_PrimaryKey3] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,    >IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS >=    ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[PrimaryKey3] ADD  CONSTRAINT    [DF_PrimaryKey3_Id]  DEFAULT (newid()) FOR [Id]
GO

    比較與建議：
    相較於 uniqueidentifier ，nvarchar 雖然佔用的儲存空間較大（數十位元組 vs. 16 位元組），但在程式設計中的應用不需要經過一個轉換文字的過程，因此建議使用。

結論與最佳實踐總結

本規範從闡述主索引鍵的策略性價值出發，透過實際案例分析了不同設計方案的優劣，接著提供了流水號與 GUID 的選擇框架，並結合企業級應用場景進行了深度剖析，最終給出了在 SQL Server 中的具體實作指南。

本文件提煉出的核心設計原則與最佳實踐

  • 優先採用代理鍵
    除非業務上有極其穩定且絕對唯一的自然鍵（如國家代碼），否則應一律另開新欄位作為主索引鍵。這能實現業務邏輯與資料實體的解耦，是構建穩健系統的基礎。
  • 預設選擇流水號
    對於絕大多數應用場景，使用 int IDENTITY 的流水號策略是最佳的預設選擇。其高效、簡潔、易讀的特性，使其在效能和可維護性上表現出色。
  • 策略性使用 GUID
    僅在特定情境下，才應採用 GUID。這些情境包括：需要在分散式環境中生成 ID、需要跨多個系統進行資料整合、或資料的階層關係必須在遷移後保持不變時（如 BOM 系統）。

深思熟慮的主索引鍵設計，是建立一個穩健、可擴展且易於維護的資料庫系統的基石。它不僅是技術決策，更是對業務未來發展的前瞻性規劃。鼓勵所有開發人員與架構師將此規範納入其日常開發實踐中，從源頭保障系統的品質。

關係圖譜