選單
探索 Prisma

PostgreSQL

PostgreSQL 列和表約束簡介

分享到

什麼是 PostgreSQL 列和表約束？

約束是可接受值的附加要求，補充了資料型別提供的內容。它們允許你為資料定義比通用資料型別中更窄的條件。

這些通常是根據應用程式提供的附加上下文對欄位特定特徵的反映。例如，一個age（年齡）欄位可能使用int資料型別來儲存整數。然而，某些可接受的整數範圍作為有效年齡是不合理的。例如，負整數在此場景中是不合理的。我們可以使用 PostgreSQL 中的約束來表達這種邏輯要求。

約束的定義位置：列約束 vs 表約束

PostgreSQL 允許你建立與特定列或整個表關聯的約束。

幾乎所有約束都可以在兩種形式中不經修改地使用

約束列表
CHECK是是
NOT NULL是否*
UNIQUE是是
PRIMARY KEY是是
FOREIGN KEY是是

*：NOT NULL不能用作表約束。但是，你可以透過在CHECK表約束中使用IS NOT NULL語句來近似達到同樣的效果。

讓我們看看列約束和表約束有何不同。

列約束

列約束是附加到單個列的約束。它們用於確定列的建議值是否有效。列約束在輸入根據基本型別要求（例如確保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),
    . . .
);

PostgreSQL 可用約束列表

既然我們已經瞭解了約束工作的一些基礎知識，我們可以深入探討有哪些可用約束以及如何使用它們。

Check 約束

Check 約束是一種通用約束，允許你指定涉及列或表值並評估為布林值的表示式。

你之前已經看過幾個 Check 約束的例子。Check 約束以關鍵字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 年內。之後，我們有一個表檢查約束，確保電影獲得了足夠的票數獲得提名，並且其長度符合“長片”類別的要求。

在評估 Check 約束時，可接受的值返回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約束更具針對性。它保證列中的值不為 null。雖然這是一個簡單的約束，但它使用非常頻繁。

如何在 PostgreSQL 中新增非空約束

要將列標記為要求非空值，請在型別聲明後新增NOT NULL。

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

在上面的示例中，我們有一個簡單的兩列表，用於將國家對映到其首都。由於這兩個都是必填欄位，留空沒有意義，因此我們添加了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).

NOT NULL約束僅作為列約束起作用（不能用作表約束）。但是，你可以在表CHECK約束中使用IS NOT NULL輕鬆解決此問題。

例如，這提供了使用表約束的等效保證。

CREATE TABLE national_capitals (
    country text,
    capital text,
    CHECK (country IS NOT NULL AND capital IS NOT NULL)
);
Prisma.io 相關內容

在使用 Prisma Client 時，你可以控制每個欄位是可選的還是強制的，以獲得與 PostgreSQL 中的NOT NULL約束等效的功能。

Prisma 是一個用於 Typescript 和 Node.js 的開源資料庫工具包，旨在讓應用程式開發者在使用資料庫時更高效、更自信。

唯一約束

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.
Prisma.io 相關內容

如果你正在使用 Prisma，你可以在你的 Prisma schema 中定義一個唯一欄位。

Prisma 是一個用於 Typescript 和 Node.js 的開源資料庫工具包，旨在讓應用程式開發者在使用資料庫時更高效、更自信。

主鍵約束

主鍵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)
);
Prisma.io 相關內容

使用 Prisma 時，主鍵與id 欄位是同義的。

Prisma 是一個用於 Typescript 和 Node.js 的開源資料庫工具包，旨在讓應用程式開發者在使用資料庫時更高效、更自信。

外部索引鍵約束

外部索引鍵是表中的列，它們引用另一個表中的列值。這在表格包含相關資料的各種場景中是可取且通常必要的。資料庫能夠輕鬆連線和引用儲存在不同表中的資料是關係型資料庫的主要特性之一。

例如，你可能有一個orders表來跟蹤單個訂單，還有一個customers表來跟蹤聯絡資訊和客戶資訊。將這些資訊分開存放是合理的，因為客戶可能有許多訂單。然而，能夠輕鬆連結這兩個表中的記錄以允許更復雜的操作也是有意義的。

Prisma.io 相關內容

使用 Prisma 時，你可以透過在不同模型之間建立關係來定義外部索引鍵。

Prisma 是一個用於 Typescript 和 Node.js 的開源資料庫工具包，旨在讓應用程式開發者在使用資料庫時更高效、更自信。

如何在 PostgreSQL 中建立外部索引鍵約束

讓我們首先嚐試對customers表進行建模。

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

這個表相當簡單。它包含用於儲存父級名字、姓氏和電話號碼的列。它還指定了一個使用PRIMARY KEY約束的 ID 列。如果未指定 ID，serial資料型別用於自動生成序列中的下一個 ID。

對於orders表，我們希望能夠指定有關單個訂單的資訊。一個重要的組成部分是哪個客戶下了訂單。我們可以使用外部索引鍵將訂單鏈接到客戶，而無需重複資訊。我們透過REFERENCES約束來完成此操作，該約束定義了與另一個表中列的外部索引鍵關係。

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

這裡，我們指示orders表中的customer列與customers表存在外部索引鍵關係。由於我們未在customers表中指定特定列，PostgreSQL 假定我們要連結到customers表中的主鍵：customer_id。

如果我們嘗試向orders表中插入一個未引用有效客戶的值，PostgreSQL 將拒絕它。

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

雖然主鍵是外部索引鍵的絕佳選擇，因為它保證只匹配一條記錄，但你也可以使用其他列，只要它們是唯一的。為此，你只需在REFERENCES定義中，在表名後的括號中指定該列即可。

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

你也可以使用保證唯一的列集。為此，你需要使用以FOREIGN KEY開頭的表約束，並引用你在表描述中早期定義的列。

CREATE TABLE example (
    . . .
    FOREIGN KEY (column1, column2) REFERENCES other_table (column1, column2)
);
Prisma.io 相關內容

我們的文件中介紹瞭如何在 Prisma schema 中定義關係。

Prisma 是一個用於 Typescript 和 Node.js 的開源資料庫工具包，旨在讓應用程式開發者在使用資料庫時更高效、更自信。

刪除或更新時如何處理外部索引鍵

在定義外部索引鍵約束時，你需要考慮的一個問題是，當引用的表被刪除或更新時該怎麼做。

例如，讓我們再次看看customers表和orders表。當客戶在orders表中有關聯訂單時，我們需要指定系統在我們從customers表中刪除客戶時應如何響應。

我們可以選擇以下選項：

  • RESTRICT（限制）：選擇限制刪除意味著如果customer記錄被orders表中的記錄引用，PostgreSQL 將拒絕刪除該customer記錄。要刪除客戶，你必須首先從orders表中刪除任何相關的記錄。只有這樣，你才能從客戶表中刪除該值。
  • CASCADE（級聯）：選擇級聯選項意味著當我們刪除customer記錄時，orders表中引用它的記錄也將被刪除。這在許多情況下都很有用，但必須小心使用，以避免誤刪資料。
  • NO ACTION（無動作）：無動作選項告訴 PostgreSQL 簡單地刪除客戶，而不對相關的orders記錄做任何操作。如果稍後檢查約束，它仍然會導致錯誤，但這不會在初始刪除期間發生。如果沒有指定其他動作，這是預設動作。
  • SET NULL（設定為空）：此選項告訴 PostgreSQL 在刪除引用的記錄時，將引用列設定為 null。因此，如果我們從customers表中刪除一個客戶，orders表中的customer列將被設定為NULL。
  • Set DEFAULT（設定為預設值）：如果選擇此選項，如果引用的記錄被刪除，PostgreSQL 會將引用列更改為預設值。因此，如果orders表中的customer列有預設值，並且我們從customers表中刪除了一個客戶，則orders值中的記錄將被賦給預設值。

這些操作可以在定義外部索引鍵約束時透過新增ON DELETE，然後跟著具體的動作來指定。因此，如果想在刪除客戶時從系統中刪除關聯訂單，可以這樣指定：

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

這些型別的操作也可以在更新引用列時應用，而不是刪除時，透過使用ON UPDATE而不是ON DELETE。

排他約束

我們要討論的最後一種約束是排他約束。雖然像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列作為一個日期範圍進行檢查。我們包含[]作為可選的第三個引數，表示範圍應包含在內進行比較。&&運算子指定日期範圍應檢查是否存在重疊。

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

總結

在本教程中，我們瞭解瞭如何使用 PostgreSQL 的約束來確定哪些特定值對於我們的表是有效的。我們討論了列約束和表約束之間的區別。之後，我們逐一介紹了各種型別的約束，並演示瞭如何使用它們來限制表接受的輸入型別。

約束是眾多功能之一，可幫助你在資料結構中定義期望。一旦提供了約束，就可以讓 PostgreSQL 驗證任何輸入是否符合要求。這是使用 PostgreSQL 資料庫系統強制保證資料保持一致和有意義的一種小方法。

關於作者
Justin Ellingwood

Justin Ellingwood

自 2013 年以來，Justin 一直從事資料庫、Linux、基礎設施和開發者工具方面的寫作。他目前與妻子和兩隻兔子住在柏林。他通常不必以第三人稱寫作，這對所有相關方來說都是一種解脫。
在 GitHub 上編輯此頁面

Prisma 資料指南

一個不斷增長的文件庫，致力於使資料庫更易於理解。

由 Prisma 用 ❤️ 製作
© . This site is unofficial and not affiliated with Prisma Data, Inc.