【DB】Set Primary key by Natural key, Composite key, or Surrogate key?

這篇主要講自然鍵、複合鍵或代理鍵做為主鍵(Primary key)的選擇。

先做下名詞解釋。

自然鍵(Natural key) 
即業務邏輯上固有的欄位，比方在個人資料裡我們可能選擇身分證字號或 email、手機等不會跟別人重複的資料做為主鍵。

複合鍵(Composite key)
當任一自然鍵無法或不適合表達該筆資料唯一性時，可整合多個自然鍵成複合鍵做為主鍵

Role
Name🔑	DramaName🔑	CostumeId	Script
大野狼	小紅帽	Wolf1	小紅帽中大野狼的腳本
大野狼	三隻小豬	Wolf1	三隻小豬中大野狼的腳本

上表為一戲劇角色建檔的資料表，我們可以看到小紅帽與三隻小豬都有大野狼這個角色，戲服也是共用的，在自然鍵中只有腳本具有識別作用，但是腳本可能是 NVarChar 的長度，要做為主鍵供它表串接，顯然不是好主意，這時我們便可選擇整合戲名與角色名做為複合主鍵。

代理鍵(Surrogate)
指創造一個不存在於業務邏輯的不重複且不為空欄為主鍵，最常見的有自動增值流水號(Auto increment)，自訂規格的值(如學號、員工證號)與 UUID(GUID)。

首先
Q. 為什麼會需要增加一個沒有業務邏輯意義的代理鍵做主鍵?
想像現在我們在做一個購物系統，用 Email 做會員的主鍵，Email 不會跟別人重複，要做忘記密碼跟認證之類的也會用到，現在註冊任何系統 Email 幾乎都是必填了，感覺上用來做主鍵還算合理。

因為會員跟交易紀錄、紅利點數、物流等關連，Email 被做為外來鍵(Foreign key)塞的到處都是，結果某天某會員突然決定他要換 Email，此時我們就必須要先修改交易記錄等所有關連表，最後才能調整會員本身的 Email。

這樣看來 Email 也不太適合，不如換成身分證吧！ 於是經歷一番功夫我們把所有外來鍵 Email 全改身分證，這次沒問題了吧？ 一年過去，業績蒸蒸日上，老闆某日開會說，我們要國際化！進軍海外！現在老外也要可以加入我們會員！

這下糗了，老外沒身分證，主鍵又要改？ 還是要把護照號碼塞到身分證欄，然後改定義為身分證或護照號碼? 聽起來不太優，一欄不該有多種定義。 或改身分證、護照兩欄複合主鍵？ 這樣要是沒護照的人要填空字串嗎？

講了這麼多，重點是業務邏輯總是有機會改變，唯一迴避連鎖修改的方式，就是一開始就隔離物理關聯與業務關聯；假設我們一開始就造一個值為 GUID 的主鍵 MemberId 給其他表串，不管我們是要改 Email 還是加護照，都對物理關聯沒有任何影響。

包含前述，使用代理鍵的主要好處有

1. 隔離業務邏輯
2. 具有比較好的效能
   1. 代理鍵比較容易做成有序，對叢集索引有幫助
   2. 自然鍵常需要複合主鍵，導致 JOIN 或 WHERE 條件增加
3. 提高可讀性、一致性(所有的外來鍵規則都是 表名+Id)

在 2.2 項的議題，完全使用自然鍵為主鍵的情況，會導致主鍵不停的被 1-N 中的 N方繼承，看下面這個稍微複雜點的案例展示

Actor
Name🔑 Gender

Drama
Name🔑 Story

Role
Name🔑 DramaName🔑 CostumeId Script

PerformRecord
DramaName🔑 PerformSeq🔑 PerformDateTime

RolePlayRecord
ActorName🔑 DramaName🔑 RoleName🔑 PerformSeq🔑

此例中，戲對角色是 1-N 關係，在角色表中為了識別各劇中的同名角色，我們引入了劇名做複合鍵，同樣的為了釐清場次與戲的關係，我們必須組合場次號與劇名做演出記錄的主鍵。 當我們需要建置演員扮演角色的演出記錄時，此表與演員是 N-1 關係，與角色表也是 N-1 關係，和演出記錄還是 N-1 的關係，所以它將被迫蒐集4個外來鍵做複合才能達成唯一識別。

也就是說
Q. 我們該總是使用代理鍵嗎？
驟下結論之前，再看看這個例子，我們現在要從工時表算 RD 部門在某專案 ProjectD 的總加班時數

SELECT 
    SUM(t.Hours) AS [TotalHours]
FROM TimeSheet t
INNER JOIN Department dep
ON t.DepartmentId = dep.Id
INNER JOIN Project proj
ON t.ProjectId = proj.Id
INNER JOIN ScheduleType schdType
ON t.ScheduleTypeId = schdType.Id
WHERE dep.Abbr = 'RD' AND
proj.Name = 'ProjectD' AND
schdType.Name = 'OverTime'

如果使用代理鍵我們正常會導出上述的查詢，但如果使用自然鍵

SELECT 
    SUM(Hours) AS [TotalHours]
FROM TimeSheet
WHERE
DepartmentId = 'RD' AND
ProjectId = 'ProjectD' AND
ScheduleTypeId = 'OverTime'

是不是一口氣簡單了很多？ 還省了 JOIN？
ref: SO

好，現在我茫了，所以
主鍵選擇的重點到底是什麼？

要回答這個問題，我們必須先釐清 什麼是主鍵?
主鍵代表資料表中各列的唯一識別，是一個非空且在各筆資料間不重複的欄位。

一張表中可能有複數欄位滿足這個條件，比方員工可能有自動增值流水號、員編、身分證、email，這些可做為主鍵的欄為我們稱為候選鍵(Candidate key)。 這樣看起來，主鍵跟加入了唯一且不為空限制的候選鍵不是一樣嗎？

Well, 並不盡然，以 MS SQL Server 為例

1. 主鍵設置時系統預設會針對它建叢集索引
2. 主鍵只能有一個(/組)，但唯一且不為空限制的候選鍵可以有多個

也就是說單論邏輯，主鍵跟其他上了限制的候選鍵沒有什麼分別，差別是在於實際應用上

1. 哪個候選鍵比較適合做叢集索引？
2. 哪個候選鍵比較適合做他表的外來鍵？

這兩個問題就是主鍵選擇的精華，可以同時符合兩者的就適合用來做主鍵，這也是為什麼我偏好流水號代理鍵，它幾乎總是同時符合兩者。

但是，如果你的應用情境沒有欄可以同時滿足兩者，比方員工號要用 GUID 簡化平行整合，但 GUID 不適合做叢集索引，流水號適合做叢集索引，但平行整合困難；這時候就需要把兩個需求拆開，選擇適合做外來鍵的候選為主鍵，再用流水號做叢集索引。 ref: SO、暗黑執行緒
※ 叢集索引跟主鍵一樣是一張表只能有一個的，但它不限定於設置為主鍵。

回到剛剛擱置的問題，我們該總是使用代理鍵嗎？
我想應該問，什麼情況下我們該選用自然鍵與複合鍵？ 會是比較好提問

以下可以參考

1. 總是處於 1-N 中1方的查找表(Lookup Table)可以用自然鍵為主鍵，例如色表 Pkey 設 ColorName = 'Red', Value = '#FF0000' 或前面說到的部門表 Pkey 設 Abbr = 'RD', Name='Research and development'
2. 表達多對多關係的連接表，只使用外來鍵的複合作主鍵，如前例演員飾演角色記錄表，它提供的功能僅是串接演員、角色與演出記錄，除了外來鍵，它本身沒有任何的欄位，因為關聯就是它意圖的全部，所以可預期它應位於終端而不會再被外部參考，就不必要再設代理鍵為主鍵。

最後在附上一些其他的參考資料：Agile Data、SO、SO2