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 约束更加集中。它保证列中的值不能为 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 约束告诉 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 约束具有特殊的用途。它表示该列可用于唯一地标识表中的记录。这意味着它必须可靠地唯一，并且每条记录都必须在该列中具有一个值。

建议每个表都有主键，但不是必须的，每个表只能有一个主键。主键主要用于识别、检索、修改或删除表中的单个记录。它们允许用户和管理员使用标识符来定位操作，该标识符由 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)
);

外键约束

外键 是一个表中引用另一个表中列值的列。在各种情况下，表包含相关数据，这非常理想，并且经常是必需的。数据库轻松连接和引用存储在不同表中的数据的能力是关系数据库的主要功能之一。

例如，您可能有一个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列一起检查为一个日期范围。我们包含[]作为可选的第三个参数，以指示应该包含范围进行比较。&&运算符指定日期范围应该检查重叠。

因此，总的来说，该约束确保在重叠日期内不会预订同一个房间。