題干與適用場景
訂單表允許每個租戶有一個可選外部參照：同一租戶最多一筆記錄可以是 NULL。你會如何在 PostgreSQL 18 實作這項約束，處理歷史重複資料、並行寫入與回滾？
PostgreSQL 的唯一索引預設把 NULL 視為彼此不同，因此多個 NULL 不會衝突。PostgreSQL 18 的 NULLS NOT DISTINCT 選項讓唯一索引把 NULL 視為相等。題目考察資料庫約束、遷移安全和並行語意，不是把規則簡單塞進應用程式。
面試官考察點
- 是否準確解釋預設 NULL 唯一性與
NULLS NOT DISTINCT的差異。 - 是否知道選項作用於唯一 B-tree 索引或唯一約束，而不是一般查詢比較。
- 是否會先找出並處理歷史重複的 NULL 和非 NULL 組合。
- 是否能設計線上遷移、鎖影響、並行寫入和失敗回滾。
- 是否評估 ORM、複製、分割和下游資料契約的相容性。
回答前需要釐清的問題
- 約束範圍是全表，還是每個租戶、區域或有效狀態一組？
- NULL 的業務含義是「尚未分配」、未知，還是明確的共用參照？
- 歷史資料是否已有多個 NULL、空字串或大小寫變體？
- 寫入流量、可接受鎖定時間和遷移窗口是多少？
- 應用、ORM、CDC 和報表是否假設 NULL 可以重複？
30 秒回答框架
「我先確認 NULL 的業務含義和約束範圍，再稽核歷史重複資料。全表規則可使用帶 NULLS NOT DISTINCT 的唯一索引或唯一約束；租戶範圍則把租戶欄位放入複合唯一鍵。遷移前清理或決策歷史衝突，線上建立索引並監測鎖和寫入延遲，驗證應用與 CDC 仍能處理唯一衝突。上線後讓資料庫成為並行下的最終裁判；若衝突處理不符合業務，就回滾約束與應用策略。」
分步驟深入解答
第一步：確認 NULL 與約束範圍
先區分「未知值」和「尚未分配」。若 NULL 表示尚未分配，最多一個 NULL 的規則可能合理；若表示未知且允許多個，就不應強行唯一。確定約束是全表還是依租戶、區域、有效狀態分組，決定複合鍵欄位順序和是否需要部分索引。
第二步：選擇資料庫表達
PostgreSQL 18 的唯一索引支援 NULLS NOT DISTINCT；預設行為是 NULL 之間不相等，允許多個 NULL。可以使用唯一約束讓資料模型更直觀，也可以直接建立唯一 B-tree 索引配合線上遷移。該選項只改變唯一索引的等值判斷，不改變 WHERE value = NULL 的三值邏輯，也不等於把欄位改成 NOT NULL。
第三步：稽核並整理歷史資料
按約束鍵分組統計多個 NULL、空字串、大小寫變體和已刪除但仍佔用鍵的記錄。為每組衝突定義業務選擇：合併訂單、補上外部參照、保留一筆並遷移其他記錄，或明確豁免。清理腳本要可重播、可稽核，先在影子環境驗證結果。
第四步：設計線上遷移
先發布相容的應用錯誤處理，再在低峰期建立唯一索引或約束。對大表評估並行建立、鎖級別、磁碟空間和寫入延遲；遷移期間持續檢查衝突和長交易。若必須逐租戶啟用，就分批建立規則並記錄完成水位。不要在資料庫約束和錯誤映射尚未就緒前刪除舊的應用預檢查。
第五步：處理並行與下游契約
資料庫唯一索引在並行插入或更新時提供最終裁決，應用的「先查再插入」只能改善體驗，不能取代約束。將唯一衝突映射成可重試或可展示的業務錯誤，避免無限重試。檢查 CDC、複製、ORM schema、報表和快取是否把 NULL 重複當成正常，並更新資料契約與告警。
第六步：驗證、監控與回滾
在 staging 和灰度租戶驗證四類組合：一個 NULL、第二個 NULL、相同非 NULL、不同非 NULL；再覆蓋更新、刪除後重建和並行寫入。監控索引建立、鎖等待、衝突率、應用錯誤和下游延遲。若業務語意不符合預期，可先停止新寫入路徑、刪除新約束、恢復相容錯誤處理，再分析原因。
高品質示範回答
我會先確認 NULL 的語意和約束範圍。若規則是每個租戶最多一個可選參照，就把租戶欄位納入複合唯一鍵，並在 PostgreSQL 18 使用 NULLS NOT DISTINCT；它讓同一鍵中的 NULL 也參與唯一性判斷，但不改變一般 SQL 三值邏輯，也不取代 NOT NULL。
上線前按租戶和鍵稽核多個 NULL、空字串、大小寫變體和軟刪除記錄，定義合併或補值方案。先發布唯一衝突的應用處理，再在低峰期建立索引或約束，監控鎖、空間、長交易和衝突。用一個 NULL、第二個 NULL、相同與不同非 NULL、更新、刪除重建和並行測試驗證。資料庫約束是最終裁判，CDC、ORM、報表和快取也要同步更新；若衝突率不可接受，按預案刪除約束並回滾應用策略。
常見錯誤
- 以為 UNIQUE 預設只允許一個 NULL → PostgreSQL 預設把 NULL 視為不同 → 明確使用
NULLS NOT DISTINCT。 - 把它當成 NOT NULL → 該選項仍允許一個 NULL → 區分缺失值語意與唯一性。
- 只在應用層先查再插入 → 並行請求仍會競態 → 讓資料庫唯一索引做最終裁決。
- 忽略空字串和大小寫變體 → 業務重複可能不表現為 NULL 衝突 → 先定義正規化和清理策略。
- 直接線上建立而不清理歷史 → 舊重複會讓遷移失敗或阻塞 → 先稽核、決策並監控長交易。
- 只改資料庫不改下游 → ORM、CDC 和報表可能仍假設 NULL 可重複 → 更新資料契約和錯誤映射。
追問及應對
NULLS NOT DISTINCT 會改變一般查詢中的 NULL 比較嗎？
不會。它改變唯一索引判斷 NULL 是否相等；WHERE value = NULL 仍遵循 SQL 三值邏輯，應使用 IS NULL。查詢語意、索引約束語意和欄位是否允許 NULL 要分開說明。
歷史上已有兩個 NULL 時，如何無停機遷移？
先按租戶和業務狀態選定保留記錄，合併或補齊其他記錄，並把決策寫入稽核表。發布衝突錯誤處理後分批建立索引，監控鎖和長交易；無法在窗口清理的租戶暫緩啟用。
多租戶規則應該用複合唯一鍵還是部分索引？
若規則是所有狀態都唯一，把租戶欄位和參照欄位放入複合唯一鍵；若只約束有效記錄，可考慮限定有效狀態的部分索引。選擇取決於刪除、恢復、狀態遷移和查詢契約，並要用並行測試證明邊界。