菜单
探索 Prisma

PostgreSQL

PostgreSQL 列和表约束简介

分享到

什么是 PostgreSQL 列和表约束？

约束是除了数据类型提供的要求之外的_额外_可接受值要求。它们允许您为数据定义比通用数据类型中更窄的条件。

这些通常反映了基于应用程序提供的额外上下文的字段特定特征。例如，age 字段可能使用 int 数据类型来存储整数。然而，某些可接受的整数范围作为有效年龄是没有意义的。例如，负整数在这种情况下是不合理的。我们可以使用约束在 PostgreSQL 中表达这种逻辑要求。

约束的定义位置：列约束与表约束

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 开头，然后提供一个用括号括起来的表达式。对于列约束，它放在数据类型声明之后。对于表约束，它们可以放在与它们交互的列定义之后的任何位置。

例如，我们可以创建一个 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 约束更加集中。它保证列中的值不为空。虽然这是一个简单的约束，但它使用非常频繁。

如何在 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 模式中定义唯一字段。

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 表是一个很好的示例。如果我们要使用现有列创建主键，我们可以用 PRIMARY KEY 替换 UNIQUE 表约束

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 模式中定义关系。

Prisma 是一个用于 Typescript 和 Node.js 的开源数据库工具包，旨在提高应用程序开发人员在使用数据库时的生产力和信心。

决定在删除或更新时如何处理外键

在定义外键约束时，您需要考虑的一个问题是如何在删除或更新引用的表时进行处理。

举个例子，让我们再次看看 customers 和 orders 表。当我们在 customers 表中删除一个客户，而该客户在 orders 表中有关联订单时，我们需要指定系统应该如何响应。

我们可以选择以下选项

  • RESTRICT：选择限制删除意味着如果 customer 记录被 orders 表中的记录引用，PostgreSQL 将拒绝删除该记录。要删除客户，您必须首先从 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

Justin 自 2013 年以来一直撰写关于数据库、Linux、基础设施和开发工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。
在 GitHub 上编辑此页面

Prisma 的数据指南

一个不断增长的文章库，旨在使数据库更易于理解。

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