分享到

什么是 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表,其中一个列是名为ageint类型。此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)
);

唯一约束

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.

如果仍希望确保不出现重复条目,同时允许单个列中存在重复值,那么对countrycapital组合进行唯一性检查就足够了。

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

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

外键约束

外键是表中的列,它们引用另一个表中的列值。这在表格包含相关数据的各种场景中是可取且通常必要的。数据库能够轻松连接和引用存储在不同表中的数据是关系型数据库的主要特性之一。

例如,你可能有一个orders表来跟踪单个订单,还有一个customers表来跟踪联系信息和客户信息。将这些信息分开存放是合理的,因为客户可能有许多订单。然而,能够轻松链接这两个表中的记录以允许更复杂的操作也是有意义的。

如何在 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)
);

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

在定义外键约束时,你需要考虑的一个问题是,当引用的表被删除或更新时该怎么做。

例如,让我们再次看看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的行。daterangebooking_startbooking_end列作为一个日期范围进行检查。我们包含[]作为可选的第三个参数,表示范围应包含在内进行比较。&&运算符指定日期范围应检查是否存在重叠。

因此,总而言之,该约束确保同一房间不会在重叠日期被预订。

总结

在本教程中,我们了解了如何使用 PostgreSQL 的约束来确定哪些特定值对于我们的表是有效的。我们讨论了列约束和表约束之间的区别。之后,我们逐一介绍了各种类型的约束,并演示了如何使用它们来限制表接受的输入类型。

约束是众多功能之一,可帮助你在数据结构中定义期望。一旦提供了约束,就可以让 PostgreSQL 验证任何输入是否符合要求。这是使用 PostgreSQL 数据库系统强制保证数据保持一致和有意义的一种小方法。

关于作者
Justin Ellingwood

Justin Ellingwood

自 2013 年以来,Justin 一直从事数据库、Linux、基础设施和开发者工具方面的写作。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作,这对所有相关方来说都是一种解脱。
© . All rights reserved.