Postgresql 中文操作指南

5.4. Constraints #

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

为此，SQL 允许你在列和表上定义约束。约束让你能任意程度地控制表中的数据。如果用户尝试将数据存储在违反约束的列中，则会引发一个错误。即使该值来自默认值定义，也适用。

5.4.1. Check Constraints #

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

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)
);

应注意，如果检查表达式计算结果为 true 或 null 值，则满足检查约束。由于如果任何运算数为 null，大多数表达式将计算结果为 null 值，因此它们不会阻止受约束列中的 null 值。为了确保某个列不包含 null 值，可以使用下一节中描述的 not-null 约束。

Note

PostgreSQL 不支持 CHECK 约束，该约束引用除了正在检查的新行或更新行以外的其他表数据。虽然违反此规则的 CHECK 约束在简单的测试中可能有效，但它不能保证数据库不会达到约束条件为假的状态（由于其他涉及行的后续更改）。这会导致数据库转储和还原失败。即使完整的数据库状态与该约束一致，但由于未按满足该约束的顺序加载行，因此还原也可能失败。如果可能，请使用 UNIQUE、EXCLUDE 或 FOREIGN KEY 约束来表示跨行和跨表限制。

如果您希望在插入行时对其他行进行一次性检查，而不是持续维护一致性保证，则可以使用自定义 trigger来实现。（这种方法避免了转储/恢复问题，因为 pg_dump 直到恢复数据后才重新安装触发器，因此在转储/恢复期间不会执行检查。）

Note

PostgreSQL 假设 CHECK 约束的条件是不可变的，即它们将始终对同一输入行给出相同的结果。此假设证明仅在插入或更新行时检查 CHECK 约束是合理的，而不是在其他时间检查。（上述关于不引用其他表数据的警告实际上是此限制的一个特例。）

破坏此假设的一个常见方法的示例是在 CHECK 表达式中引用用户定义函数，然后更改该函数的行为。PostgreSQL 不禁止这样做，但如果表中有现在违反 CHECK 约束的行，它将不会注意到。这种情况会导致后续的数据库转储和还原失败。处理此类更改的建议方法是删除约束（使用 ALTER TABLE），调整函数定义，然后重新添加约束，从而针对所有表行重新检查它。

5.4.2. Not-Null Constraints #

not-null 约束仅仅指定某个列不能采用 null 值。语法示例：

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

not-null 约束始终写为列约束。not-null 约束在功能上等效于创建检查约束 CHECK (_column_name IS NOT NULL)），但在 PostgreSQL 中创建显式的 not-null 约束效率更高。缺点是您无法为通过这种方式创建的 not-null 约束提供显式名称。

当然，某个列可以具有多个约束。只需逐个编写约束：

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

顺序无关紧要。它并不一定决定按什么顺序检查约束。

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

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

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

Tip

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

5.4.3. Unique Constraints #

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

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 树索引。仅覆盖部分行的唯一性限制不能写为唯一约束，但可以通过创建唯一 partial index来强制实施此类限制。

通常，如果表中一行以上的值等于约束中包含的所有列的值，则会违反唯一约束。默认情况下，此比较中两个空值不被视为相等。这意味着即使存在唯一约束，也可以存储包含至少一个约束列空值的重复行。可以通过添加 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. Primary Keys #

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

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-tree 索引，并会强制将列标记为 NOT NULL。

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

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

5.4.5. Foreign Keys #

外键约束指定列（或列组）中的值必须与另一表的某一行的值匹配。我们称此维持了两个相关表之间的 referential integrity。

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

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

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

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

现在，无法创建包含不显示在 products 表中的非 NULL product_no 条目的订单。

我们认为，在这种情况下，orders 表是 referencing 表，products 表是 referenced 表。类似地，有引用列和被引用列。

您也可以将上面的命令缩短为：

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)
);

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

有时将外键约束的“另一张表”设为同表是很有用的；这称为 self-referential 外键。例如，如果您希望表的行表示树结构的节点，您可以写成

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

顶级节点将为 NULL parent_id，而非 NULL parent_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 操作可能合适。例如，如果产品表中包含对产品经理的引用，并且产品经理条目被删除，那么将产品的产品经理设为 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 来自引用列需要扫描引用表以查找与旧值匹配的行，因此通常对引用列编制索引也是一个好主意。由于并不总是需要这么做，并且在如何编制索引方面有很多可用的选择，所以外键约束的声明不会自动在引用列上创建索引。

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

5.4.6. Exclusion Constraints #

排除约束确保了如果使用指定的操作符在指定列或表达式上对任意两行进行比较，则至少一个这些操作符比较将返回假或空值。语法：

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

还可参阅 CREATE TABLE … CONSTRAINT … EXCLUDE 以获取详细信息。

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