5.1 簡介
Internet 上充斥著關於應如何在關係數據庫中選擇和使用鍵的教條式規則。有時爭論甚至會演化為 holivars：應該使用自然密鑰還是人工密鑰？自動遞增整數或 UUID？
在閱讀了 64 篇文章，翻閱了 5 本書的章節，並在 IRC 和 StackOverflow 上提出了大量問題之後，我（Joe “begriffs” Nelson，原文章的作者）似乎已經把拼圖的各個部分放在一起並且現在可以調和對手。很多關鍵性的爭論其實都是因為誤解了別人的觀點而產生的。
讓我們把問題分開，最後把它重新組合起來。首先，讓我們問一個問題——什麼是“鑰匙”？
讓我們暫時忘記主鍵，我們對更一般的想法感興趣。鍵是一列（column）或在行中沒有重複值的列。此外，列必須是不可簡化的唯一性，即列的任何子集都沒有這種唯一性。
但首先，一些理論：
首要的關鍵
首要的關鍵直接用於標識表中的行。它必須遵守以下限制：
- 主鍵必須始終是唯一的。
- 它必須始終存在於表中並具有值。
- 它不應該經常改變它的價值。理想情況下，它根本不應該改變值。
通常，主鍵表示表的單個列，但它也可以是由多個列組成的組合鍵。
組合鍵
自定義鍵- 唯一標識每個表行的屬性（列）組合。它可以是所有列，也可以是幾列，也可以是一列。在這種情況下，不應重複包含這些屬性值的行。
潛在鑰匙
候選鍵- 表示關係（表）的最小組合鍵，即一組滿足若干條件的屬性：
- 不可約性：它不能被約化，它包含最小可能的屬性集。
- 唯一性：無論行如何變化，它都必須具有唯一值。
- 值的存在：它不能有空值，即它必須有一個值。
5.2 主鍵的奇怪情況
我們在上一節中所說的“鍵”通常被稱為“候選鍵”。術語“候選”意味著所有這些鍵都在競爭“主鍵”（primary key）的榮譽角色，其餘的則分配給“替代鍵”（alternate keys）。
SQL 實現需要一段時間才能克服鍵與關係模型之間的不匹配問題，並且最早的數據庫面向主鍵的低級概念。需要此類數據庫中的主鍵來標識一行在順序存儲介質上的物理位置。以下是 Joe Celko 的解釋：
術語“鍵”是指文件排序鍵，它是在順序文件系統上執行任何處理操作所必需的。一套穿孔卡片只按一個順序閱讀；回去是不可能的。早期的磁帶驅動器模仿相同的行為並且不允許雙向訪問。也就是說，原始的 Sybase SQL Server 需要將表“倒帶”到開頭以讀取上一行。
在現代 SQL 中，您不需要關注信息的物理表示、表模型關係，行的內部順序根本不重要。然而，即使是現在，SQL Server 也默認為主鍵創建聚集索引，並且根據舊傳統，以物理方式排列行的順序。
在大多數數據庫中，主鍵已成為過去，僅提供反射或物理位置。例如，在 PostgreSQL 表中，聲明主鍵會自動強制執行約束NOT NULL並定義默認外鍵。此外，主鍵是 operator 的首選列JOIN。
主鍵不會覆蓋聲明其他鍵的可能性。同時，如果沒有鍵被指定為主鍵，那麼該表仍然可以正常工作。無論如何，閃電不會擊中你。
5.3 尋找自然鍵
上面討論的鍵被稱為“自然”，因為它們是建模對象的屬性，它們本身很有趣，即使沒有人想用它們製作鍵。
在為可能的自然鍵檢查表時要記住的第一件事是盡量不要太聰明。StackExchange 上的用戶 sqlvogel 給出了以下建議：
有些人很難選擇“自然”鍵，因為他們會想出某個特定鍵可能不是唯一的假設情況。他們不明白這項任務的真正意義。鍵的含義是定義規則，根據該規則，屬性在任何給定時間必須並且始終在特定表中是唯一的。該表包含特定且易於理解的上下文中的數據（在“主題區域”或“話語區域”中），唯一的含義是限制在該特定區域中的應用。
實踐表明，當列對於可用值是唯一的並且在可能的場景中將保持這種情況時，有必要引入鍵約束。如果有必要，可以取消限制（如果這讓您感到困擾，那麼下面我們將討論密鑰穩定性。）
例如，愛好俱樂部成員的數據庫可能在兩列中具有唯一性 - first_name, last_name. 對於少量數據，重複是不可能的，在真正的衝突出現之前，使用這樣的密鑰是很合理的。
隨著數據庫的增長和信息量的增加，選擇自然鍵會變得更加困難。我們存儲的數據是對外部現實的簡化，不包含區分世界上物體的某些方面，例如它們隨時間變化的坐標。如果一個物體沒有任何代碼，你如何區分兩罐飲料或兩盒燕麥片的空間排列或重量或包裝的細微差別？
這就是標準化機構為產品創建和應用獨特標記的原因。車輛印有車輛識別碼 (VIN)，書籍印有ISBN，食品包裝有UPC。你可能會反對這些數字看起來不自然。那麼為什麼我稱它們為自然鍵呢？
數據庫中獨特屬性的自然性或人為性是相對於外部世界而言的。在標準機構或政府機構中創建時是人為的密鑰對我們來說變得很自然，因為它成為全世界的標準和/或印在物體上。
有許多針對各種主題的行業、公共和國際標準，包括貨幣、語言、金融工具、化學品和醫療診斷。下面是一些經常用作自然鍵的值：
- ISO 3166 國家代碼
- ISO 639 語言代碼
- 符合 ISO 4217 的貨幣代碼
- 股票代號 ISIN
- UPC/EAN、VIN、GTIN、ISBN
- 登錄名
- 電子郵件地址
- 房間號碼
- 網絡MAC地址
- 地球表麵點的緯度、經度
我建議盡可能合理地聲明鍵，甚至每個表可能有多個鍵。但請記住，以上所有情況都可能有例外。
- 並非每個人都有電子郵件地址，儘管在某些數據庫條件下這可能是可以接受的。此外，人們會不時更改他們的電子郵件地址。（稍後將詳細介紹密鑰穩定性。）
- ISIN 股票代碼會不時更改，例如代碼 GOOG 和 GOOGL 不能準確描述公司從 Google 到 Alphabet 的重組。有時會出現混淆，例如 TWTR 和 TWTRQ，一些投資者在 Twitter IPO 期間錯誤地購買了後者。
- 社會安全號碼僅供美國公民使用，具有隱私限制，並且在死後可以重複使用。此外，文件被盜後，人們可以獲得新的號碼。最後，同一個數字可以同時識別一個人和一個所得稅標識符。
- 郵政編碼對於城市來說是一個糟糕的選擇。有些城市有一個共同的指標，反之亦然，一個城市有多個指標。
5.4 人工鑰匙
鑑於鍵是每一行都有唯一值的列，創建它的一種方法是作弊——你可以在每一行中寫入虛構的唯一值 \u200b\u200bin。這些是人造鍵：用於引用數據或對象的發明代碼。
代碼是從數據庫本身生成的，並且除了數據庫用戶之外任何人都不知道，這一點非常重要。這就是人工密鑰與標準化自然密鑰的區別。
雖然自然鍵的優勢在於防止表中出現重複或不一致的行，但人工鍵很有用，因為它們使人類或其他系統更容易引用該行，並且它們加快了查找和連接的速度，因為它們不使用字符串（或多列）比較。鍵。
代理人
人工鍵用作錨點——無論規則和列如何變化，始終可以以相同的方式識別一行。用於此目的的人工密鑰稱為“代理密鑰”，需要特別注意。我們將在下面考慮代理人。
非代理人工鍵對於從數據庫外部引用行很有用。人工密鑰可簡要標識數據或對象：它可以指定為 URL、附在發票上、通過電話口述、從銀行獲得或印在車牌上。（汽車的牌照對我們來說是一把天然鑰匙，但被政府設計成了一把人造鑰匙。）
選擇合成密鑰時應考慮到可能的傳輸方式，以盡量減少錯別字和錯誤。應該注意的是，密鑰可以說出、閱讀打印、通過 SMS 發送、閱讀手寫、從鍵盤鍵入和嵌入 URL。此外，一些人工密鑰（例如信用卡號）包含校驗和，因此如果出現某些錯誤，它們至少可以被識別。
例子：
- 對於美國車牌，有關於使用模糊字符的規則，例如 O 和 0。
- 考慮到醫生的筆跡，醫院和藥房必須特別小心。
- 您是否通過短信發送確認碼？不要超出 GSM 03.38 字符集。
- 與編碼任意字節數據的 Base64 不同，Base32 使用有限的字符集，便於人類在較舊的計算機系統上使用和處理。
- Proquints 是可讀、可寫和可發音的標識符。這些是明確理解的輔音和元音的 PRO-nouncable QUINT-uplets。
請記住，一旦您向世界介紹您的人造鑰匙，人們就會奇怪地開始給予它特別的關注。只要看看“小偷”車牌或創建可發音標識符的系統，它已成為臭名昭著的自動詛咒生成器。
即使我們局限於數字鍵，也有像第十三層那樣的禁忌。雖然 proquints 每個語音音節的信息密度更高，但數字在很多方面也很好：在 URL、pin 鍵盤和手寫筆記中，只要接收者知道密鑰只是數字。
但是，請注意，您不應在公共數字鍵中使用連續順序，因為這允許您翻閱資源（/videos/1.mpeg、/videos/2.mpeg 等）並且還會洩露有關數字的信息數據。在數字序列上疊加 Feistel 網絡並在隱藏數字順序的同時保持唯一性。
反對聲明額外鍵的唯一論據是每個新鍵都會帶來另一個唯一索引並增加寫入表的成本。當然，這取決於數據的正確性對您有多重要，但最有可能的是，仍應聲明密鑰。
如果有的話，也值得聲明幾個人工密鑰。例如，一個組織有求職者（Applicants）和僱員（Employees）。每個員工都曾經是候選人，並通過自己的標識符引用候選人，這也應該是員工的關鍵。再比如，你可以在Employees中設置employee id和login name作為兩個key。
5.5 代理鍵
如前所述，一種重要的人工密鑰類型稱為“代理密鑰”。它不需要像其他人造鍵一樣簡潔和通行，而是用作始終標識字符串的內部標籤。它在 SQL 中使用，但應用程序不會顯式訪問它。
如果您熟悉 PostgreSQL 的系統列，那麼您幾乎可以將代理項視為數據庫實現參數（如 ctid），但是它永遠不會改變。代理值每行選擇一次，此後永遠不會改變。
代理鍵與外鍵一樣出色，必須指定級聯約束ON UPDATE RESTRICT以匹配代理項的不變性。
另一方面，公共共享密鑰的外鍵應標有ON UPDATE CASCADE，以提供最大的靈活性。級聯更新運行在與周圍事務相同的隔離級別，所以不要擔心並發問題——如果您選擇嚴格的隔離級別，數據庫會很好。
不要讓代理鍵“自然”。一旦您向最終用戶顯示代理鍵的值，或者更糟的是，讓他們使用該值（特別是通過查找），您實際上就是在為鍵賦予一個值。然後，您數據庫中顯示的密鑰可以成為其他人數據庫中的自然密鑰。
強制外部系統使用其他專為傳輸而設計的人工密鑰，使我們能夠根據需要更改這些密鑰以滿足不斷變化的需求，同時通過代理保持內部參照完整性。
自增 INT/BIGINT
代理鍵最常見的用途是自動遞增的“bigserial”列，也稱為IDENTITY。（實際上，PostgreSQL 10 現在支持 IDENTITY 構造，Oracle 也是如此，請參閱 CREATE TABLE。）
但是，我認為自動遞增的整數對於代理鍵來說不是一個好的選擇。這個意見不受歡迎，所以讓我解釋一下。
串行密鑰的缺點：
- 如果所有序列都從 1 開始並遞增，那麼來自不同表的行將具有相同的鍵值。這個選項並不理想，在表中使用不相交的鍵集仍然更可取，這樣，例如，查詢就不會意外混淆常量
JOIN並返回意外結果。（或者，為了確保沒有交集，可以從不同素數的倍數構造每個序列，但這會相當費力。） - 在當今的分佈式 SQL 中調用
nextval()生成序列會導致整個系統無法很好地擴展。 - 從同樣使用順序鍵的數據庫中消費數據會導致衝突，因為順序值在整個系統中不是唯一的。
- 從哲學的角度來看，數字的連續增加與隱含線條順序的舊系統有關。如果您現在想要對行進行排序，請使用時間戳列或在您的數據中有意義的內容明確地進行排序。否則，違反第一範式。
- 理由不充分，但這些簡短的標識符很容易告訴別人。
UUID
讓我們看看另一種選擇：使用根據隨機模式生成的大整數（128 位）。生成此類通用唯一標識符 (UUID) 的算法兩次選擇相同值的可能性極低，即使同時在兩個不同的處理器上運行也是如此。
在那種情況下，UUID似乎是用作代理鍵的自然選擇，不是嗎？如果您想以獨特的方式標記行，那麼沒有什麼比獨特的標籤更好的了！
那麼為什麼不是每個人都在 PostgreSQL 中使用它們呢？這有幾個人為的原因和一個可以解決的合乎邏輯的原因，我將提供基準來說明我的觀點。
首先，我會談談牽強的原因。有些人認為 UUID 是字符串，因為它們是用帶有破折號的傳統十六進製表示法編寫的：5bd68e64-ff52-4f54-ace4-3cd9161c8b7f。事實上，一些數據庫沒有緊湊的（128 位）uuid 類型，但 PostgreSQL 有並且大小為 two bigint，也就是說，與數據庫中的其他信息量相比，開銷可以忽略不計。
UUID 也被不公平地指責為笨重，但誰來發音、輸入或閱讀它們呢？我們說過顯示人工密鑰是有意義的，但沒有人（根據定義）應該看到代理 UUID。UUID 可能會由開發人員在 psql 中運行 SQL 命令以調試系統來處理，但僅此而已。如果給定，開發人員還可以使用更方便的鍵來引用字符串。
UUID 的真正問題在於，高度隨機化的值會導致寫放大，因為整頁寫入預寫日誌 (WAL)。然而，性能下降實際上取決於 UUID 生成算法。
讓我們測量寫放大。實際上，問題出在較舊的文件系統中。當 PostgreSQL 寫入磁盤時，它會更改磁盤上的“頁面”。如果關閉計算機電源，大多數文件系統在數據安全存儲到磁盤之前仍會報告寫入成功。如果 PostgreSQL 天真地認為這樣的操作已完成，那麼數據庫將在下一次系統啟動期間損壞。
由於 PostgreSQL 不能信任大多數操作系統/文件系統/磁盤配置來提供連續性，因此數據庫將修改後的磁盤頁面的完整狀態保存到預寫日誌中，可用於從可能的崩潰中恢復。索引高度隨機化的值（如 UUID）通常涉及一堆不同的磁盤頁面，並導致將整個頁面大小（通常為 4 或 8 KB）寫入每個新條目的 WAL。這就是所謂的整頁寫（full-page write，FPW）。
一些 UUID 生成算法（例如 Twitter 的“snowflake”或 PostgreSQL 的 uuid-ossp 擴展中的 uuid_generate_v1()）在每台機器上生成單調遞增的值。這種方法將寫入合併到更少的磁盤頁面並減少 FPW。
5.6 結論和建議
現在我們已經了解了不同類型的密鑰及其用途，我想列出我在數據庫中使用它們的建議。
對於每個表：
- 定義並聲明所有自然鍵。
- 創建一個UUID
<table_name>_id類型的代理鍵，默認值為. 您甚至可以將其標記為主鍵。如果將表的名稱添加到此標識符，這將簡化，即 接收而不是. 不要將此密鑰傳遞給客戶端，也不要將其暴露在數據庫之外。uuid_generate_v1()JOINJOIN foo USING (bar_id)JOIN foo ON (foo.bar_id = bar.id) - 對於通過 的中間表
JOIN，將所有外鍵列聲明為單個複合主鍵。 - （可選）添加可在 URL 或其他字符串引用指示中使用的人工鍵。使用Feistel 網格或pg_hashids屏蔽掉自動遞增的整數。
ON UPDATE RESTRICT使用代理 UUID 作為外鍵和人工外鍵指定級聯約束ON UPDATE CASCADE。根據您自己的邏輯選擇自然鍵。
這種方法確保內部密鑰的穩定性，同時允許甚至保護自然密鑰。此外，可見的人造鍵不會附加到任何東西上。正確理解所有內容後，您不能隻掛在“主鍵”上並使用所有使用鍵的可能性。