正確性與約束

簡介

儲存資料是一回事；儲存有意義、有用且正確的資料則完全是另一回事。雖然意義和效用本身是主觀的質量，但正確性至少可以在邏輯上定義和強制執行。型別已經確保數字是數字，日期是日期，但無法保證重量或距離是正數，也無法防止日期範圍重疊。元組、表和資料庫約束對儲存的資料應用規則，並拒絕不符合要求的數值或數值組合。

約束絕不會使其他輸入驗證技術變得無用，即使它們測試相同的斷言。嘗試儲存無效資料但失敗所花費的時間是浪費時間。違反訊息，例如系統和應用程式程式語言中的 assert，僅以比非直接資料庫相關人員所需更詳細的方式揭示第一個候選記錄的第一個問題。但就資料的正確性而言，約束是法律，無論是好是壞；其他一切都只是建議。

關於元組：非空、預設值和檢查

非空約束是最簡單的類別。元組必須為受約束的屬性指定一個值，或者換句話說，該列允許的值集合不再包含空集。沒有值意味著沒有元組：插入或更新將被拒絕。

防止空值就像在 CREATE TABLE 或 ADD COLUMN 中宣告 column_name COLUMN_TYPE NOT NULL 一樣簡單。空值會在資料庫和終端使用者之間引發各種問題，因此，如果沒有充分的理由允許空值，本能地對任何列定義非空約束是一個好習慣。

在插入或更新中，如果未指定任何內容（透過省略或顯式 NULL）而提供預設值，通常不被視為約束，因為候選記錄會被修改並存儲而不是被拒絕。在許多 DBMS 中，預設值可以由函式生成，儘管 MySQL 不允許為此目的使用使用者定義的函式。

任何其他僅依賴於單個元組內值的驗證規則都可以作為 CHECK 約束來實現。從某種意義上說，NOT NULL 本身就是 CHECK (column_name IS NOT NULL) 的簡寫；主要的區別在於違反時收到的錯誤訊息。CHECK 可以應用並強制執行單個元組上任何布林謂詞的真實性。例如，儲存地理位置的表應 CHECK (latitude >= -90 AND latitude < 90)，經度類似地介於 -180 和 180 之間——或者，如果可用，使用並驗證 GEOGRAPHY 資料型別。

關於表：唯一性和排他性

表級約束相互測試元組。在唯一約束中，只有一條記錄可以對受約束的列擁有給定的一組值。可為空性可能在此處引起問題，因為 NULL 永遠不等於其他任何東西，包括 NULL 本身。因此，對 (batman, robin) 的唯一約束允許無限複製任何沒有 Robin 的 Batman。

排他性約束僅在 PostgreSQL 和 DB2 中受支援，但它們填補了一個非常有用的空白：它們可以防止重疊。指定受約束的欄位以及評估每個欄位的操作，只有當沒有現有記錄與每個欄位和操作成功比較時，新記錄才會被接受。例如，一個 schedules 表可以配置為拒絕衝突

-- text, int, etc. comparisons in exclusion constraints require this
-- Postgres extension
CREATE EXTENSION btree_gist;

CREATE TABLE schedules (
  schedule_id SERIAL NOT NULL PRIMARY KEY,
  room_number TEXT NOT NULL,
  -- a range of TIMESTAMP WITH TIME ZONE provides both start and end
  duration TSTZRANGE,
  -- table-level constraints imply an index, since otherwise they'd
  -- have to search the entire table to validate a candidate record;
  -- GiST (generalized search tree) indexes are usually used in
  -- Postgres
  EXCLUDE USING GIST (
    room_number WITH =,
    duration WITH &&
  )
);

INSERT INTO schedules (room_number, duration)
VALUES ('32A', '[2020-08-20T10:00:00Z,2020-08-20T11:00:00Z)');

-- the same time in a different room: accepted
INSERT INTO schedules (room_number, duration)
VALUES ('32B', '[2020-08-20T10:00:00Z,2020-08-20T11:00:00Z)');

-- a half-hour overlap for an already-scheduled room: rejected
INSERT INTO schedules (room_number, duration)
VALUES ('32A', '[2020-08-20T10:30:00Z,2020-08-20T11:30:00Z)');

Upsert 操作（例如 PostgreSQL 的 ON CONFLICT 子句或 MySQL 的 ON DUPLICATE KEY UPDATE）使用表級約束來檢測衝突。就像非空約束可以表示為 CHECK 約束一樣，唯一約束可以表示為等價的排他性約束。

主鍵

唯一約束有一個特別有用的特殊情況。如果對唯一列或多列新增非空約束，則表中的每條記錄都可以透過其受約束列的值進行唯一標識，這些列統稱為鍵。表中可以共存多個候選鍵，例如 users 表有時仍具有不同的唯一且非空的 email 和 username；但宣告主鍵建立了一個單一的準則，透過該準則記錄被公開且唯一地識別。某些 RDBMS 甚至透過主鍵在頁面上組織行，為此目的稱之為聚簇索引，以使透過主鍵值進行搜尋儘可能快。

主鍵有兩種型別。自然鍵是在表中“自然”包含的列上定義的，而代理鍵或合成鍵則是完全為了作為鍵的目的而建立的。自然鍵需要謹慎——許多事物比資料庫設計者通常認為的更容易改變，從名稱到編號方案。包含國家和地區名稱的查詢表可以使用它們各自的 ISO 3166 程式碼作為安全的自然主鍵，但 users 表如果使用基於可變值（如姓名或電子郵件地址）的自然鍵則會招致麻煩。當不確定時，建立代理鍵。

如果自然鍵跨多個列，則至少應始終考慮使用代理鍵，因為多列鍵管理起來需要更多精力。然而，如果自然鍵合適，列的順序應按特異性遞增排列，就像在索引中一樣：國家程式碼然後地區程式碼，而不是反過來。

代理鍵在歷史上一直是一個整數列，或者在最終將分配數十億個值的情況下是 BIGINT。關係型資料庫可以自動用系列中的下一個整數填充代理鍵，此功能通常稱為 SERIAL 或 IDENTITY。

自增數字計數器並非沒有缺點：新增帶有預生成鍵的記錄可能導致衝突，如果將序列值暴露給使用者，他們很容易猜測其他有效的鍵可能是什麼。全域性唯一識別符號（UUID）避免了這些弱點，並已成為代理鍵的常見選擇，儘管它們在頁面內也比簡單的數字大得多。v1（基於 MAC 地址）和 v4（偽隨機）UUID 型別是最常用的。

關於資料庫：外部索引鍵

關係型資料庫只實現一種多表約束，即“子集要求”或外部索引鍵。這種唯一的約束型別是引用完整性的保證者，該原則可防止表之間出現不一致，並將關係型資料庫與電子表格區分開來。

這個非正式的“實體關係圖”或 ERD 展示了圖書館及其藏書和讀者資料庫的 Schema 的最初形態。每條邊代表它所連線的表之間的關係。| 符號表示其一側的單個記錄，而“烏鴉腳”符號表示多個：一個圖書館擁有許多書籍和許多讀者。

外部索引鍵是另一個表主鍵的副本，逐列複製（支援代理鍵的一點：只需複製和引用一列），其值將此表中的記錄連結到該表中的“父”記錄。在上面的 Schema 中，books 表維護一個指向 libraries 的 library_id 外部索引鍵（圖書館藏書），以及一個指向 authors 的 author_id（作者創作書籍）。但是，如果插入一本書時，其 author_id 在 authors 中不存在，會發生什麼？

如果外部索引鍵沒有約束——即，它只是另一列或多列——一本書可能有一個不存在的作者。這是一個問題：如果有人試圖跟隨 books 和 authors 之間的連結，他們將一無所獲。如果 authors.author_id 是一個序列整數，那麼也有可能直到最終分配了虛假的 author_id 才有人注意到，結果你發現特定版本的《堂吉訶德》首先歸於未知作者，然後歸於皮埃爾·梅納德，而米格爾·塞萬提斯卻無處可尋。

即使錯誤的 author_id 指向 authors 中存在的記錄，對外部索引鍵施加約束也無法阻止書籍被錯誤歸屬，因此其他檢查和測試仍然很重要。然而，現有外部索引鍵值的集合幾乎總是可能外部索引鍵值的一個微小子集，因此外部索引鍵約束將捕獲並防止大多數錯誤值。有了外部索引鍵約束，沒有不存在作者的《堂吉訶德》將被拒絕，而不是被記錄。

對於某些“正確”的值

資料不正確的方式遠不止本文所討論的這些。約束有所幫助，但它們的靈活性也有限；許多常見的表內規範，例如限制某個值在列中出現的次數不超過兩次或更多次，只能透過觸發器來強制執行。

但表的結構本身也可能導致不一致。為了防止這些，我們將需要利用主鍵和外部索引鍵，不僅用於定義和驗證，還要用於規範化表之間的關係。然而，首先，我們才剛剛觸及表之間的關係如何定義資料庫本身的結構的皮毛。

常見問題