5.4. 约束#

5.4.1. 检查约束

5.4.2. 非空约束

5.4.3. 唯一约束

5.4.4. 主键

5.4.5. 外键

5.4.6. 排除约束

数据类型是一种限制表中可存储数据类型的方法。但是，对于许多应用程序而言，它们提供的约束太粗略。例如，包含产品价格的列可能只接受正值。但是，没有标准数据类型只接受正数。另一个问题是，您可能希望针对其他列或行约束列数据。例如，在包含产品信息的表中，每个产品编号应只有一行。

为此，SQL 允许您对列和表定义约束。约束让您可以根据需要对表中的数据进行尽可能多的控制。如果用户尝试将数据存储在违反约束的列中，则会引发错误。即使该值来自默认值定义，也适用此规则。

5.4.1. 检查约束#

检查约束是最通用的约束类型。它允许您指定某个列中的值必须满足布尔值（真值）表达式。例如，要要求正的产品价格，您可以使用

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

如您所见，约束定义位于数据类型之后，就像默认值定义一样。默认值和约束可以按任何顺序列出。检查约束由关键字CHECK组成，后面跟着括号中的表达式。检查约束表达式应涉及受此约束的列，否则该约束将毫无意义。

您还可以为约束指定一个单独的名称。这可以阐明错误消息，并允许您在需要更改约束时引用该约束。语法为

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

因此，要指定命名约束，请使用关键字CONSTRAINT，后面跟一个标识符，再跟约束定义。（如果您不以这种方式指定约束名称，系统会为您选择一个名称。）

检查约束还可以引用多个列。假设您存储了一个常规价格和一个折扣价，并且您希望确保折扣价低于常规价格

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

前两个约束应该看起来很熟悉。第三个约束使用了新的语法。它没有附加到特定列，而是作为逗号分隔的列列表中的一个单独项目出现。列定义和这些约束定义可以按混合顺序列出。

我们说前两个约束是列约束，而第三个是表约束，因为它单独于任何一列定义而编写。列约束也可以写成表约束，而反过来则不一定可能，因为列约束应该只引用它所附加的列。（PostgreSQL并不强制执行该规则，但如果你希望你的表定义与其他数据库系统一起使用，则应该遵循该规则。）上述示例还可以写成

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

或甚至

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

这取决于个人喜好。

可以像给列约束分配名称一样给表约束分配名称

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

应当注意，如果检查表达式计算结果为真或空值，则满足检查约束。由于大多数表达式在任何操作数为空时都会计算为空值，因此它们不会阻止受约束列中的空值。若要确保列不包含空值，可以使用下一部分中描述的非空约束。

注意

PostgreSQL不支持引用正在检查的新行或更新行以外的表数据的CHECK约束。虽然违反此规则的CHECK约束在简单测试中可能看起来有效，但它不能保证数据库不会达到约束条件为假的状态（由于涉及的其他行随后发生更改）。这会导致数据库转储和恢复失败。即使完整的数据库状态与约束一致，恢复也可能失败，因为行没有按满足约束的顺序加载。如果可能，请使用UNIQUE、EXCLUDE或FOREIGN KEY约束来表示跨行和跨表限制。如果你想要的是在行插入时对其他行进行一次性检查，而不是持续维护一致性保证，则可以使用自定义触发器来实现。（这种方法避免了转储/恢复问题，因为pg_dump在恢复数据之前不会重新安装触发器，因此在转储/恢复期间不会强制执行检查。）

注意

PostgreSQL假定CHECK约束的条件是不可变的，即它们对于相同的输入行总是会给出相同的结果。此假设证明了仅在插入或更新行时检查CHECK约束的合理性，而其他时候不检查。PostgreSQL假定CHECK约束的条件是不可变的，即它们对于相同的输入行总是会给出相同的结果。此假设证明了仅在插入或更新行时检查CHECK约束的合理性，而其他时候不检查。（上面关于不引用其他表数据的警告实际上是此限制的一个特例。）打破此假设的一种常见方法的示例是在CHECK表达式中引用用户定义函数，然后更改该函数的行为。PostgreSQL并不禁止这样做，但它不会注意到表中现在违反CHECK约束的行。这会导致后续的数据库转储和恢复失败。处理此类更改的建议方法是删除约束（使用ALTER TABLE），调整函数定义，然后重新添加约束，从而针对所有表行重新检查它。

5.4.2. 非空约束#

非空约束仅仅指定一列不得采用空值。语法示例

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

非空约束始终写为列约束。非空约束在功能上等效于创建检查约束CHECK (*column_name*IS NOT NULL)，但在PostgreSQL中创建显式非空约束更有效。缺点是您无法为通过这种方式创建的非空约束指定显式名称。

当然，一列可以有多个约束。只需将约束一个接一个地写出来

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

顺序无关紧要。它不一定决定检查约束的顺序。

NOT NULL约束有一个反向：NULL约束。这并不意味着该列必须为空，这肯定毫无用处。相反，这只是选择列可能为空的默认行为。NULL约束不在 SQL 标准中，不应在可移植应用程序中使用。（它仅添加到PostgreSQL中以与某些其他数据库系统兼容。）然而，一些用户喜欢它，因为它可以轻松地在脚本文件中切换约束。例如，您可以从

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

开始，然后在需要的地方插入NOT关键字。

提示

在大多数数据库设计中，大多数列都应标记为非空。

5.4.3. 唯一约束#

唯一约束确保表中所有行中一列或一组列包含的数据是唯一的。语法为

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

作为列约束编写时，以及

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

作为表约束编写时。

要为一组列定义唯一约束，请将其作为表约束编写，列名用逗号分隔

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

这指定了所指示列中的值组合在整个表中是唯一的，尽管任何一列都不需要（通常也不是）唯一。

您可以按照通常的方式为唯一约束分配自己的名称

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

添加唯一约束将自动在约束中列出的列或列组上创建唯一的 B 树索引。仅涵盖某些行的唯一性限制不能写为唯一约束，但可以通过创建唯一的部分索引来强制执行此类限制。

通常，如果表中存在多于一行，其中包含在约束中包含的所有列的值相等，则会违反唯一约束。默认情况下，在此比较中不将两个空值视为相等。这意味着即使存在唯一约束，也可以存储在至少一个受约束列中包含空值的重复行。可以通过添加子句NULLS NOT DISTINCT来更改此行为，例如

CREATE TABLE products (
    product_no integer UNIQUE NULLS NOT DISTINCT,
    name text,
    price numeric
);

或

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE NULLS NOT DISTINCT (product_no)
);

可以使用NULLS DISTINCT显式指定默认行为。根据 SQL 标准，唯一约束中的默认空值处理是实现定义的，其他实现具有不同的行为。因此，在开发旨在可移植的应用程序时要小心。

5.4.4. 主键#

主键约束表示一列或一组列可以用作表中行的唯一标识符。这要求值既唯一又非空。因此，以下两个表定义接受相同的数据

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

主键可以跨多个列；语法类似于唯一约束

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

添加主键将自动在主键中列出的列或列组上创建唯一的 B 树索引，并将强制将列标记为NOT NULL。

一个表最多只能有一个主键。（可以有任何数量的唯一且非空约束，它们在功能上几乎是相同的东西，但只能将一个标识为主键。）关系数据库理论规定每个表都必须有一个主键。此规则不受PostgreSQL强制执行，但通常最好遵循它。

主键对于文档目的和客户端应用程序都很有用。例如，允许修改行值的 GUI 应用程序可能需要知道表的主键才能唯一标识行。数据库系统还有各种方法可以利用已声明的主键；例如，主键定义了引用其表的外键的默认目标列。

5.4.5. 外键#

外键约束指定一列（或一组列）中的值必须与另一张表中某一行的值匹配。我们称之为维护两个相关表之间的引用完整性。

假设您拥有我们已经多次使用的产品表

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

我们还假设您有一个存储这些产品的订单表。我们希望确保订单表仅包含实际存在的产品的订单。因此，我们在订单表中定义了一个外键约束，该约束引用产品表

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

现在，不可能创建具有不为 NULL 的product_no条目的订单，这些条目未出现在产品表中。

我们说在这种情况下，订单表是引用表，产品表是被引用表。类似地，有引用列和被引用列。

您还可以将上述命令缩短为

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

因为在没有列列表的情况下，被引用表的主键用作被引用列。

您可以按照通常的方式为外键约束分配自己的名称。

外键还可以约束和引用一组列。通常，它需要以表约束形式编写。这是一个虚构的语法示例

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

当然，受约束列的数量和类型需要与被引用列的数量和类型匹配。

有时，外键约束的“其他表”是同一张表很有用；这称为自引用外键。例如，如果您希望表中的行表示树结构的节点，则可以编写

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);

顶级节点将具有 NULLparent_id，而非 NULLparent_id条目将受到约束，以引用表中的有效行。

一张表可以有多个外键约束。这用于实现表之间的多对多关系。假设您有关于产品和订单的表，但现在您希望允许一个订单包含可能包含许多产品（上面的结构不允许这样做）。您可以使用此表结构

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

请注意，主键与最后一张表中的外键重叠。

我们知道外键不允许创建与任何产品无关的订单。但是，如果在创建引用了某个产品的订单之后删除了该产品，该怎么办？SQL 也允许你处理这种情况。凭直觉，我们有几个选择

• 不允许删除引用的产品

• 同时删除订单

• 其他操作？

为了说明这一点，让我们对上面多对多关系示例实现以下策略：当有人想要删除仍被订单引用的产品（通过order_items）时，我们不允许这样做。如果有人删除订单，则订单项也将被删除

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

限制和级联删除是最常见的两个选项。RESTRICT阻止删除引用的行。NO ACTION意味着如果在检查约束时仍存在任何引用行，则会引发错误；这是未指定任何内容时的默认行为。（这两个选择之间的本质区别在于，NO ACTION允许将检查推迟到事务的稍后时间，而RESTRICT不允许。）CASCADE指定在删除引用的行时，引用它的行也应自动删除。还有两个其他选项：SET NULL和SET DEFAULT。当引用的行被删除时，这些选项会导致引用行中的引用列分别设置为 null 或其默认值。请注意，这些选项并不能免除你遵守任何约束。例如，如果某个操作指定了SET DEFAULT，但默认值不满足外键约束，则该操作将失败。

适当选择ON DELETE操作取决于相关表表示的对象类型。当引用表表示的是被引用表所表示内容的组件并且不能独立存在时，CASCADE可能合适。如果这两个表表示的是独立对象，则RESTRICT或NO ACTION更合适；实际上想要删除这两个对象的应用程序必须明确说明这一点并运行两个删除命令。在上面的示例中，订单项是订单的一部分，如果订单被删除，自动删除订单项会很方便。但是，产品和订单是不同的东西，因此让删除产品自动导致删除某些订单项可能会被认为是有问题的。如果外键关系表示可选信息，则操作SET NULL或SET DEFAULT可能合适。例如，如果 products 表包含对产品经理的引用，并且产品经理条目被删除，那么将产品的产品经理设置为 null 或默认值可能很有用。

操作SET NULL和SET DEFAULT可以采用列列表来指定要设置的列。通常，外键约束的所有列都将被设置；仅设置一个子集在某些特殊情况下很有用。考虑以下示例

CREATE TABLE tenants (
    tenant_id integer PRIMARY KEY
);

CREATE TABLE users (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    user_id integer NOT NULL,
    PRIMARY KEY (tenant_id, user_id)
);

CREATE TABLE posts (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    post_id integer NOT NULL,
    author_id integer,
    PRIMARY KEY (tenant_id, post_id),
    FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
);

如果没有指定列，外键也会将列tenant_id设置为 null，但该列仍然是主键的一部分。

类似于ON DELETE，还有ON UPDATE，当引用的列被更改（更新）时调用。可能的操作是相同的，除了SET NULL和SET DEFAULT不能指定列列表。在这种情况下，CASCADE表示引用的列的更新值应复制到引用行中。

通常，如果引用行的任何引用列为 null，则该引用行不必满足外键约束。如果将MATCH FULL添加到外键声明中，则只有当引用行的所有引用列都为 null 时，引用行才能避免满足约束（因此，null 和非 null 值的混合必定会使MATCH FULL约束失败）。如果您不希望引用行能够避免满足外键约束，请将引用列声明为NOT NULL。

外键必须引用是主键或形成唯一约束的列，或是非部分唯一索引中的列。这意味着引用的列始终具有索引，以便有效查找引用行是否匹配。由于从引用表中DELETE一行或UPDATE一个引用列将需要扫描引用表以查找与旧值匹配的行，因此通常最好也对引用列编制索引。由于并不总是需要这样做，并且有很多方法可用于编制索引，因此外键约束的声明不会自动在引用列上创建索引。

有关更新和删除数据的更多信息，请参阅第 6 章。另请参阅CREATE TABLE的参考文档中有关外键约束语法的说明。

5.4.6. 排除约束#

排除约束确保如果使用指定的操作符对指定列或表达式比较任意两行，则至少一个这些操作符比较将返回 false 或 null。语法为

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

有关详细信息，另请参阅CREATE TABLE ... CONSTRAINT ... EXCLUDE。

添加排除约束将自动创建约束声明中指定的类型的索引。