PostgreSQL 欄位和表格約束簡介

欄位約束

欄位約束是附加到單個欄位的約束。它們用於確定欄位的建議值是否有效。在根據基本類型要求（例如確保 int 欄位的值是整數）驗證輸入後，會評估欄位約束。

欄位約束非常適合表達僅限於單個欄位的要求。它們將約束條件直接附加到相關欄位。例如，我們可以透過在欄位名稱和資料類型之後添加約束，在 person 表格中對 age 限制進行建模

CREATE TABLE person (
    . . .
    age int CHECK (age >= 0),
    . . .
);

此程式碼片段定義了一個 person 表格，其中一個欄位是名為 age 的 int。 age 必須大於或等於零。欄位約束很容易理解，因為它們作為額外要求添加到它們影響的欄位上。

表格約束

另一種類型的約束稱為表格約束。表格約束可以表達欄位約束可以表達的任何限制，但還可以額外表達涉及多個欄位的限制。表格約束不是附加到特定欄位，而是定義為表格的單獨組件，並且可以參考表格的任何欄位。

我們之前看到的欄位約束可以表達為如下的表格約束

CREATE TABLE person (
    . . .
    age int,
    . . .
    CHECK (age >= 0)
);

使用了相同的基本語法，但約束是單獨列出的。為了利用表格約束引入複合限制的能力，我們可以使用邏輯 AND 運算符來連接來自不同欄位的多個條件。

例如，在銀行資料庫中，名為 qualified_borrowers 的表格可能需要檢查個人是否擁有現有帳戶以及提供抵押品的能力，以便有資格獲得貸款。將這兩者都包含在同一個檢查中可能是有意義的

CREATE TABLE qualified_borrowers (
    . . .
    account_number int,
    acceptable_collateral boolean,
    . . .
    CHECK (account_number IS NOT NULL AND acceptable_collateral = 't')
);

在這裡，我們再次使用 CHECK 約束來檢查 account_number 是否為空，以及貸款專員是否透過檢查 acceptable_collateral 欄位，將客戶標記為擁有可接受的抵押品。由於正在檢查多個欄位，因此表格約束是必要的。

現在是提及的好時機，雖然我們主要在這些範例中使用 CREATE TABLE SQL 命令來建立新表格，但您也可以使用 ALTER TABLE 將約束添加到現有表格。當使用 ALTER TABLE 時，預設情況下，新的約束會導致表格中目前的值根據新的約束進行檢查。您可以透過包含 NOT VALID 子句來跳過此行為。

預設約束名稱

當您使用上述語法建立約束時，PostgreSQL 會自動選擇一個合理但含糊不清的名稱。在上面的 qualified_borrowers 表格的例子中，PostgreSQL 會將約束命名為 qualified_borrowers_check

INSERT INTO qualified_borrowers VALUES (123, false);

ERROR:  new row for relation "qualified_borrowers" violates check constraint "qualified_borrowers_check"
DETAIL:  Failing row contains (123, f).

當約束被違反時，這個名稱會提供關於表格和約束類型的資訊。但是，如果表格上存在多個約束，更具描述性的名稱有助於疑難排解。

自訂約束名稱

您可以選擇性地指定約束的名稱，方法是在約束定義前面加上 CONSTRAINT 關鍵字，後跟名稱。

添加自訂名稱的基本語法如下

CONSTRAINT <constraint_name> <constraint_type_and_details>

例如，如果您想將 qualified_borrowers 表格中的約束命名為 loan_worthiness，您可以改為像這樣定義表格

CREATE TABLE qualified_borrowers (
    . . .
    account_number int,
    acceptable_collateral boolean,
    . . .
    CONSTRAINT loan_worthiness CHECK (account_number IS NOT NULL AND acceptable_collateral = 't')
);

現在，當我們違反約束時，我們會得到更具描述性的標籤

INSERT INTO qualified_borrowers VALUES (123, false);

ERROR:  new row for relation "qualified_borrowers" violates check constraint "loan_worthiness"
DETAIL:  Failing row contains (123, f).

您可以以相同的方式命名欄位約束

CREATE TABLE teenagers (
    . . .
    age int CONSTRAINT is_teenager CHECK (age >= 13 AND age <= 19),
    . . .
);

檢查約束

檢查約束是一種通用約束，允許您指定一個涉及欄位或表格值的表達式，該表達式評估為布林值。

您之前已經看過一些檢查約束的範例。檢查約束以關鍵字 CHECK 開頭，然後提供括在括號中的表達式。對於欄位約束，這放在資料類型宣告之後。對於表格約束，這些可以放在與它們交互的欄位定義後的任何位置。

例如，我們可以建立一個 film_nominations 表格，其中包含已獲得提名並有資格獲得 2019 年劇情片獎的電影

CREATE TABLE film_nominations (
    title text,
    director varchar(250),
    release_date date CHECK ('01-01-2019' <= release_date AND release_date <= '12-31-2019'),
    length int,
    votes int,
    CHECK (votes >= 10 AND length >= 40)
);

我們有一個欄位檢查約束，用於檢查 release_date 是否在 2019 年內。之後，我們有一個表格檢查約束，確保影片已收到足夠的票數以獲得提名，並且片長符合「劇情片」類別的資格。

在評估檢查約束時，可接受的值會傳回 true。如果新紀錄的值滿足所有類型要求和約束，則該紀錄將被添加到表格中

INSERT INTO film_nominations VALUES (
    'A great film',
    'Talented director',
    '07-16-2019',
    117,
    45
);

INSERT 0 1

產生 false 的值會產生錯誤，表明約束未滿足

INSERT INTO film_nominations VALUES (
    'A poor film',
    'Misguided director',
    '10-24-2019',
    128,
    1
);

ERROR:  new row for relation "film_nominations" violates check constraint "film_nominations_check"
DETAIL:  Failing row contains (A poor film, Misguided director, 2019-07-16, 128, 1).

在本例中，影片滿足了除所需票數之外的所有條件。PostgreSQL 拒絕提交，因為它未通過最終的表格檢查約束。

Not null 約束

NOT NULL 約束更加集中。它保證欄位中的值不為空值。雖然這是一個簡單的約束，但它使用非常頻繁。

CREATE TABLE national_capitals (
    country text NOT NULL,
    capital text NOT NULL,
);

INSERT INTO national_capitals VALUES (
    NULL,
    'London',
);

ERROR:  null value in column "country" violates not-null constraint
DETAIL:  Failing row contains (null, London).

CREATE TABLE national_capitals (
    country text,
    capital text,
    CHECK (country IS NOT NULL AND capital IS NOT NULL)
);

Unique 約束

UNIQUE 約束告訴 PostgreSQL 欄位中的每個值都不得重複。這在許多不同的情境中都很有用，在這些情境中，在多個紀錄中具有相同的值應該是不可能的。

例如，任何類型的 ID 欄位，根據定義，都應該具有唯一值。社會安全號碼、學生或客戶 ID 或產品 UPC（條碼號碼）如果無法區分特定的人或項目，將毫無用處。

UNIQUE 約束可以在欄位層級指定

CREATE TABLE supplies (
    supply_id integer UNIQUE,
    name text,
    inventory integer
);

它們也可以指定為表格約束

CREATE TABLE supplies (
    supply_id integer,
    name text,
    inventory integer,
    UNIQUE (supply_id)
);

使用 UNIQUE 表格約束的優點之一是，它允許您對欄位組合執行唯一性檢查。這是透過指定兩個或多個 PostgreSQL 應一起評估的欄位來實現的。個別欄位中的值可能會重複，但指定的值組合必須是唯一的。

作為範例，讓我們回顧一下我們之前使用的 national_capitals 表格

CREATE TABLE national_capitals (
    country text NOT NULL,
    capital text NOT NULL,
);

如果我們想確保我們不會為同一對添加多個紀錄，我們可以在此處為欄位添加 UNIQUE 約束

CREATE TABLE national_capitals (
    country text NOT NULL UNIQUE,
    capital text NOT NULL UNIQUE,
);

這將確保國家和首都都只在每個表格中出現一次。但是，有些國家有多個首都。這表示我們可能有具有相同 country 值的多個條目。這些不適用於目前的設計

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'La Paz'
);

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "national_capitals_country_key"
DETAIL:  Key (country)=(Bolivia) already exists.

如果我們仍然想確保我們不會最終得到重複的條目，同時允許個別欄位中重複的值，那麼對 country 和 capital 的組合進行唯一性檢查就足夠了

CREATE TABLE national_capitals (
    country text,
    capital text,
    UNIQUE (country, capital)
);

現在，我們可以將玻利維亞的兩個首都添加到表格中而不會出錯

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'La Paz'
);

INSERT 0 1
INSERT 0 1

但是，嘗試再次添加相同的組合仍然會被約束捕獲

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "national_capitals_country_capital_key"
DETAIL:  Key (country, capital)=(Bolivia, Sucre) already exists.

Primary key 約束

PRIMARY KEY 約束具有特殊用途。它指示該欄位可用於唯一識別表格中的紀錄。這表示它必須是可靠的唯一性，並且每個紀錄都必須在該欄位中具有值。

建議每個表格都使用主鍵，但並非必要，而且每個表格只能有一個主鍵。主鍵主要用於識別、檢索、修改或刪除表格中的個別紀錄。它們允許使用者和管理員使用 PostgreSQL 保證完全匹配一個紀錄的識別符來定位操作。

讓我們使用我們之前看到的 supplies 表格作為範例

CREATE TABLE supplies (
    supply_id integer UNIQUE,
    name text,
    inventory integer
);

在這裡，我們已確定 supply_id 應該是唯一的。如果我們想使用此欄位作為我們的主鍵（保證唯一性和非空值），我們可以簡單地將 UNIQUE 約束變更為 PRIMARY KEY

CREATE TABLE supplies (
    supply_id integer PRIMARY KEY,
    name text,
    inventory integer
);

這樣，如果我們需要更新特定供應品的庫存量，我們可以使用主鍵來定位它

INSERT INTO supplies VALUES (
    38,
    'nails',
    5
);
UPDATE supplies set inventory = 10 WHERE supply_id = 38;

INSERT 0 1
UPDATE 1

雖然許多表格使用單個欄位作為主鍵，但也可以使用一組欄位建立主鍵，作為表格約束。

national_capitals 表格是展示這一點的好候選者。如果我們想使用現有欄位建立主鍵，我們可以將 UNIQUE 表格約束替換為 PRIMARY KEY

CREATE TABLE national_capitals (
    country text,
    captial text,
    PRIMARY KEY (country, capital)
);

Foreign key 約束

Foreign key 是一個表格中參考另一個表格中欄位值的欄位。這在各種情境中是理想的並且通常是必要的，在這些情境中，表格包含相關資料。資料庫輕鬆連接和參考儲存在不同表格中的資料的能力是關聯式資料庫的主要功能之一。

例如，您可能有一個 orders 表格來追蹤個別訂單，以及一個 customers 表格來追蹤聯絡資訊和關於您客戶的資訊。將此資訊分開是有意義的，因為客戶可能有許多訂單。但是，能夠輕鬆連結這兩個表格中的紀錄以允許更複雜的操作也是有意義的。

CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    first_name text,
    last_name text,
    phone_number bigint,
);

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    order_date date,
    customer integer REFERENCES customers
);

INSERT INTO orders VALUES (
    100,
    '11-19-2019',
    300
);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_fkey"
DETAIL:  Key (customer)=(300) is not present in table "customers".

INSERT INTO customers VALUES (
    300,
    'Jill',
    'Smith',
    '5551235677'
);
INSERT INTO orders VALUES (
    100,
    '11-19-2019',
    300
);

INSERT 0 1
INSERT 0 1

CREATE TABLE example (
    . . .
    column type REFERENCES other_table (column)
);

CREATE TABLE example (
    . . .
    FOREIGN KEY (column1, column2) REFERENCES other_table (column1, column2)
);

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    order_date date,
    customer integer REFERENCES customers ON DELETE CASCADE
);

排除約束

我們將討論的最後一種約束類型是排除約束。雖然像 CHECK 這樣的約束可以個別檢查每列的有效性，但排除約束會相互檢查多列的值。UNIQUE 約束是一種特定的排除約束類型，它檢查每列在相關欄位或欄位組合中是否具有不同的值。

例如，您可以使用排除約束來確保兩個日期範圍之間沒有重疊，排除方式如下所示

CREATE EXTENSION btree_gist;

CREATE TABLE bookings (
    room int,
    booking_start date,
    booking_end date,
    EXCLUDE USING gist (
        room WITH =,
        daterange(booking_start, booking_end, '[]') WITH &&
    )
);

在這裡，我們有一個飯店預訂的建立表格語句，包含房間號碼以及預訂開始和結束日期。首先，指定 CREATE EXTENSION btree_gist 以確保我們將使用的索引方法已在資料庫中啟用。之後，我們使用 EXCLUDE USING 語法新增排除約束。我們指定 gist 作為索引方法，這告訴 PostgreSQL 如何索引和存取值以進行比較。

然後，我們列出想要比較項目方式。我們指定 room 值應該使用等號進行比較，這表示約束只會比對具有相同 room 的兩列。daterange 將 booking_start 和 booking_end 欄位一起作為日期範圍進行檢查。我們包含 [] 作為可選的第三個參數，以指示範圍應該包含在內地進行比較。&& 運算子指定日期範圍應該檢查重疊。

因此，總而言之，此約束確保同一間房間不會在重疊的日期被預訂。