正確性與約束條件

簡介

儲存資料是一回事；儲存有意義、有用、正確的資料又是另一回事。雖然意義和實用性本身就是主觀的品質，但至少正確性可以在邏輯上定義和強制執行。類型已經確保數字是數字，日期是日期，但無法保證重量或距離是正數，或防止日期範圍重疊。元組、表格和資料庫約束條件將規則應用於正在儲存的資料，並拒絕不符合標準的值或值組合。

約束條件絕非會使其他輸入驗證技術變得無用，即使它們測試相同的斷言也是如此。花時間嘗試並未能儲存無效資料是浪費時間。違規訊息，就像系統和應用程式程式語言中的 assert 一樣，只會比任何不直接參與資料庫的人員需要的更詳細地揭示第一個候選記錄的第一個問題。但就資料的正確性而言，約束條件是法則，無論好壞；其他任何東西都只是建議。

關於元組：not null、default 和 check

非空約束條件是最簡單的類別。元組必須具有受約束屬性的值，或者換句話說，資料行的允許值集合不再包含空集合。沒有值表示沒有元組：插入或更新將被拒絕。

防止空值就像在 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 資料類型。

關於表格：unique 和 exclusion

表格層級約束條件會互相測試元組。在唯一約束條件中，只有一個記錄可以針對受約束的資料行具有任何給定的值集合。空值可能會在此處引起問題，因為 NULL 永遠不等於任何其他值，包括 NULL 本身。因此，(batman, robin) 上的唯一約束條件允許無限複製任何沒有羅賓的蝙蝠俠。

排除約束條件僅在 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 顯示了資料庫結構描述的開端，該結構描述用於圖書館及其藏書和讀者的資料庫。每條邊緣代表它所連接的表格之間的關係。| 符號表示其側面的單個記錄，而「雞爪」符號表示多個：一個圖書館擁有許多書籍並擁有許多讀者。

外鍵是另一個表格主鍵的副本，逐個資料行複製（這是有利於代理鍵的一點：只有一個資料行要複製和參考），其值將此表格中的記錄連結到該表格中的「父」記錄。在上面的結構描述中，books 表格維護一個指向 libraries 的 library_id 外鍵（圖書館持有書籍），以及指向 authors 的 author_id 外鍵（作者撰寫書籍）。但是，如果插入的書籍的 author_id 在 authors 中不存在，會發生什麼情況？

如果外鍵不受約束——即，它只是另一個資料行或多個資料行——則書籍可以有一個不存在的作者。這是一個問題：如果有人試圖追蹤 books 和 authors 之間的連結，他們最終會無處可去。如果 authors.author_id 是一個序列整數，也可能在虛假 author_id 最終被分配之前沒有人注意到，並且您最終會得到一本特定的《唐吉訶德》副本，首先歸因於不知名的人，然後歸因於皮耶·梅納爾，而米格爾·德·塞凡提斯無處可尋。

約束外鍵無法防止書籍被錯誤歸屬，如果錯誤的 author_id 指向 authors 中的現有記錄，則其他檢查和測試仍然很重要。但是，現有的外鍵值集合幾乎總是可能的外鍵值的一個小子集，因此外鍵約束條件將捕獲並防止大多數錯誤值。使用外鍵約束條件，具有不存在作者的《唐吉訶德》將被拒絕而不是記錄。

關於正確性的特定值

資料可能不正確的方式遠遠超出此處討論的範圍。約束條件有所幫助，但即使它們也只能在一定程度上靈活；許多常見的表格內規格，例如限制一個值在資料行中允許出現的次數為兩次或更多次，只能使用觸發器來強制執行。

但是表格的結構本身也可能導致不一致。為了防止這些情況發生，我們需要整理主鍵和外鍵，不僅要定義和驗證，還要正規化表格之間的關係。但是，首先，我們幾乎還沒有觸及 表格之間的關係如何定義資料庫本身的結構的表面。

常見問題