Postgresql 中文操作指南
5.4. Constraints #
数据类型是一种限制可在表中存储的数据类型的方法。然而，对于许多应用程序来说，它们提供的约束太粗略。例如，包含产品价格的列可能只接受正值。但是，没有标准数据类型只接受正数。另一个问题是，你可能希望针对其他列或行来约束列数据。例如，在包含产品信息的表中，对于每个产品编号应只存在一行。
Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. For example, a column containing a product price should probably only accept positive values. But there is no standard data type that accepts only positive numbers. Another issue is that you might want to constrain column data with respect to other columns or rows. For example, in a table containing product information, there should be only one row for each product number.
为此，SQL 允许你在列和表上定义约束。约束让你能任意程度地控制表中的数据。如果用户尝试将数据存储在违反约束的列中，则会引发一个错误。即使该值来自默认值定义，也适用。
To that end, SQL allows you to define constraints on columns and tables. Constraints give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised. This applies even if the value came from the default value definition.
5.4.1. Check Constraints #
检查约束是最通用的约束类型。它允许你指定特定列中的值必须满足布尔（真值）表达式的要求。例如，若要要求正产品价格，你可以使用：
A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression. For instance, to require positive product prices, you could use:
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);正如您所见，约束定义紧跟数据类型，就像默认值定义一样。默认值和约束可以按任何顺序列出。检查约束由关键字 CHECK 组成，后面用括号括住一个表达式。检查约束表达式应该涉及被约束的列，否则约束不会太有意义。
As you see, the constraint definition comes after the data type, just like default value definitions. Default values and constraints can be listed in any order. A check constraint consists of the key word CHECK followed by an expression in parentheses. The check constraint expression should involve the column thus constrained, otherwise the constraint would not make too much sense.
您还可以为约束指定一个单独的名称。这可以阐明错误消息，并且允许您在需要更改它时引用该约束。语法为：
You can also give the constraint a separate name. This clarifies error messages and allows you to refer to the constraint when you need to change it. The syntax is:
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);所以，要指定命名约束，请使用关键字 CONSTRAINT，后面跟一个标识符，再跟约束定义。（如果您不以这种方式指定约束名称，系统将为您选择一个名称。）
So, to specify a named constraint, use the key word CONSTRAINT followed by an identifier followed by the constraint definition. (If you don’t specify a constraint name in this way, the system chooses a name for you.)
检查约束还可以引用多列。比如您存储常规价格和促销价格，并且您想要确保促销价格低于常规价格：
A check constraint can also refer to several columns. Say you store a regular price and a discounted price, and you want to ensure that the discounted price is lower than the regular price:
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);前两个约束应该是相似的。第三个约束使用了一个新的语法。它没有附加到特定列，而是作为逗号分隔列列表中的一个单独项目出现。列定义和这些约束定义可以按混合顺序列出。
The first two constraints should look familiar. The third one uses a new syntax. It is not attached to a particular column, instead it appears as a separate item in the comma-separated column list. Column definitions and these constraint definitions can be listed in mixed order.
我们说前两个约束是列约束，而第三个约束是表约束，因为它与任何一个列定义分开编写。列约束也可以写为表约束，但反过来却不一定可行，因为列约束应该仅引用它附加到的列。（PostgreSQL 并不强制执行该规则，但是如果您希望您的表定义与其他数据库系统一起使用，则应该遵循该规则。）上述示例也可以写成：
We say that the first two constraints are column constraints, whereas the third one is a table constraint because it is written separately from any one column definition. Column constraints can also be written as table constraints, while the reverse is not necessarily possible, since a column constraint is supposed to refer to only the column it is attached to. (PostgreSQL doesn’t enforce that rule, but you should follow it if you want your table definitions to work with other database systems.) The above example could also be written as:
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);甚至：
or even:
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0 AND price > discounted_price)
);这只是个人喜好问题。
It’s a matter of taste.
可以像列约束一样为表约束分配名称：
Names can be assigned to table constraints in the same way as column constraints:
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 约束。
It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. To ensure that a column does not contain null values, the not-null constraint described in the next section can be used.
Note
PostgreSQL 不支持 CHECK 约束，该约束引用除了正在检查的新行或更新行以外的其他表数据。虽然违反此规则的 CHECK 约束在简单的测试中可能有效，但它不能保证数据库不会达到约束条件为假的状态（由于其他涉及行的后续更改）。这会导致数据库转储和还原失败。即使完整的数据库状态与该约束一致，但由于未按满足该约束的顺序加载行，因此还原也可能失败。如果可能，请使用 UNIQUE、EXCLUDE 或 FOREIGN KEY 约束来表示跨行和跨表限制。
PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that the database will not reach a state in which the constraint condition is false (due to subsequent changes of the other row(s) involved). This would cause a database dump and restore to fail. The restore could fail even when the complete database state is consistent with the constraint, due to rows not being loaded in an order that will satisfy the constraint. If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express cross-row and cross-table restrictions.
如果您希望在插入行时对其他行进行一次性检查，而不是持续维护一致性保证，则可以使用自定义 trigger来实现。（这种方法避免了转储/恢复问题，因为 pg_dump 直到恢复数据后才重新安装触发器，因此在转储/恢复期间不会执行检查。）
If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained consistency guarantee, a custom trigger can be used to implement that. (This approach avoids the dump/restore problem because pg_dump does not reinstall triggers until after restoring data, so that the check will not be enforced during a dump/restore.)
Note
PostgreSQL 假设 CHECK 约束的条件是不可变的，即它们将始终对同一输入行给出相同的结果。此假设证明仅在插入或更新行时检查 CHECK 约束是合理的，而不是在其他时间检查。（上述关于不引用其他表数据的警告实际上是此限制的一个特例。）
PostgreSQL assumes that CHECK constraints' conditions are immutable, that is, they will always give the same result for the same input row. This assumption is what justifies examining CHECK constraints only when rows are inserted or updated, and not at other times. (The warning above about not referencing other table data is really a special case of this restriction.)
破坏此假设的一个常见方法的示例是在 CHECK 表达式中引用用户定义函数，然后更改该函数的行为。PostgreSQL 不禁止这样做，但如果表中有现在违反 CHECK 约束的行，它将不会注意到。这种情况会导致后续的数据库转储和还原失败。处理此类更改的建议方法是删除约束（使用 ALTER TABLE），调整函数定义，然后重新添加约束，从而针对所有表行重新检查它。
An example of a common way to break this assumption is to reference a user-defined function in a CHECK expression, and then change the behavior of that function. PostgreSQL does not disallow that, but it will not notice if there are rows in the table that now violate the CHECK constraint. That would cause a subsequent database dump and restore to fail. The recommended way to handle such a change is to drop the constraint (using ALTER TABLE), adjust the function definition, and re-add the constraint, thereby rechecking it against all table rows.
5.4.2. Not-Null Constraints #
not-null 约束仅仅指定某个列不能采用 null 值。语法示例：
A not-null constraint simply specifies that a column must not assume the null value. A syntax example:
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 约束提供显式名称。
A not-null constraint is always written as a column constraint. A not-null constraint is functionally equivalent to creating a check constraint CHECK (_column_name IS NOT NULL)_, but in PostgreSQL creating an explicit not-null constraint is more efficient. The drawback is that you cannot give explicit names to not-null constraints created this way.
当然，某个列可以具有多个约束。只需逐个编写约束：
Of course, a column can have more than one constraint. Just write the constraints one after another:
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);顺序无关紧要。它并不一定决定按什么顺序检查约束。
The order doesn’t matter. It does not necessarily determine in which order the constraints are checked.
NOT NULL 约束有一个相反的约束：NULL 约束。这并不意味着该列必须为 null，那显然毫无用处。相反，这只是选择列可能为 null 的默认行为。NULL 约束在 SQL 标准中不存在，并且不应在可移植应用程序中使用。（仅在 PostgreSQL 中添加它以与其他一些数据库系统兼容。）但是，一些用户喜欢它，因为它使在脚本文件中切换约束变得很容易。例如，您可以从：
The NOT NULL constraint has an inverse: the NULL constraint. This does not mean that the column must be null, which would surely be useless. Instead, this simply selects the default behavior that the column might be null. The NULL constraint is not present in the SQL standard and should not be used in portable applications. (It was only added to PostgreSQL to be compatible with some other database systems.) Some users, however, like it because it makes it easy to toggle the constraint in a script file. For example, you could start with:
CREATE TABLE products (
product_no integer NULL,
name text NULL,
price numeric NULL
);然后在需要的地方插入 NOT 关键字。
and then insert the NOT key word where desired.
Tip
在大多数数据库设计中，大多数列都应标记为 not null。
In most database designs the majority of columns should be marked not null.
5.4.3. Unique Constraints #
唯一约束确保表中所有行的列或列组中包含的数据唯一。语法为：
Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. The syntax is:
CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);作为列约束编写时，以及：
when written as a column constraint, and:
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);作为表约束编写时。
when written as a table constraint.
要为一组列定义唯一约束，请将其写为表约束，列名用逗号分隔：
To define a unique constraint for a group of columns, write it as a table constraint with the column names separated by commas:
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);这指定了指示的列中值的组合在整个表中是唯一的，尽管列中的任何一列不必（并且通常不会）是唯一的。
This specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn’t) unique.
您可以按通常的方式为唯一约束分配自己的名称：
You can assign your own name for a unique constraint, in the usual way:
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);添加唯一约束将自动在约束中列出的列或列组上创建唯一的 B 树索引。仅覆盖部分行的唯一性限制不能写为唯一约束，但可以通过创建唯一 partial index来强制实施此类限制。
Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.
通常，如果表中一行以上的值等于约束中包含的所有列的值，则会违反唯一约束。默认情况下，此比较中两个空值不被视为相等。这意味着即使存在唯一约束，也可以存储包含至少一个约束列空值的重复行。可以通过添加 NULLS NOT DISTINCT 子句来更改此行为，例如
In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. By default, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior can be changed by adding the clause NULLS NOT DISTINCT, like
CREATE TABLE products (
product_no integer UNIQUE NULLS NOT DISTINCT,
name text,
price numeric
);或
or
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE NULLS NOT DISTINCT (product_no)
);可以使用 NULLS DISTINCT 显式指定默认行为。唯一约束中的默认空处理根据 SQL 标准由实现定义，其他实现具有不同的行为。因此，在开发意在可移植的应用程序时要小心。
The default behavior can be specified explicitly using NULLS DISTINCT. The default null treatment in unique constraints is implementation-defined according to the SQL standard, and other implementations have a different behavior. So be careful when developing applications that are intended to be portable.
5.4.4. Primary Keys #
主键约束表示列或列组可用作表中行的唯一标识符。这需要值既唯一又非空。因此，以下两个表定义接受相同的数据：
A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null. So, the following two table definitions accept the same data:
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
);主键可以跨越多个列；语法类似于唯一约束：
Primary keys can span more than one column; the syntax is similar to unique constraints:
CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);添加主键将自动在主键中列出的列或列组上创建一个唯一的 B-tree 索引，并会强制将列标记为 NOT NULL。
Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key, and will force the column(s) to be marked NOT NULL.
一个表最多只能有一个主键。（可以有任何数量的唯一和非空约束，它们在功能上几乎相同，但只能将一个识别为主键。）关系数据库理论要求每个表都必须有一个主键。PostgreSQL 并不强制执行此规则，但通常最好遵循此规则。
A table can have at most one primary key. (There can be any number of unique and not-null constraints, which are functionally almost the same thing, but only one can be identified as the primary key.) Relational database theory dictates that every table must have a primary key. This rule is not enforced by PostgreSQL, but it is usually best to follow it.
主键既可用于文档目的，也可用于客户端应用程序。例如，允许修改行值的 GUI 应用程序可能需要知道表的主键才能唯一识别行。如果已声明主键，数据库系统有很多方式可以利用它；例如，主键定义了引用其表的外部键的默认目标列。
Primary keys are useful both for documentation purposes and for client applications. For example, a GUI application that allows modifying row values probably needs to know the primary key of a table to be able to identify rows uniquely. There are also various ways in which the database system makes use of a primary key if one has been declared; for example, the primary key defines the default target column(s) for foreign keys referencing its table.
5.4.5. Foreign Keys #
外键约束指定列（或列组）中的值必须与另一表的某一行的值匹配。我们称此维持了两个相关表之间的 referential integrity。
A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.
假设您已拥有我们多次使用过的产品表：
Say you have the product table that we have used several times already:
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);我们还假设您有一个表来存储这些产品的订单。我们希望确保订单表仅包含实际存在的产品的订单。因此，我们在 orders 表中定义了一个引用 products 表的外键约束：
Let’s also assume you have a table storing orders of those products. We want to ensure that the orders table only contains orders of products that actually exist. So we define a foreign key constraint in the orders table that references the products table:
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);现在，无法创建包含不显示在 products 表中的非 NULL product_no 条目的订单。
Now it is impossible to create orders with non-NULL product_no entries that do not appear in the products table.
我们认为，在这种情况下，orders 表是 referencing 表，products 表是 referenced 表。类似地，有引用列和被引用列。
We say that in this situation the orders table is the referencing table and the products table is the referenced table. Similarly, there are referencing and referenced columns.
您也可以将上面的命令缩短为：
You can also shorten the above command to:
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products,
quantity integer
);因为如果缺少列列表，则引用表的主键将用作引用列。
because in absence of a column list the primary key of the referenced table is used as the referenced column(s).
您可以按照通常的方式为外键约束分配自己的名称。
You can assign your own name for a foreign key constraint, in the usual way.
外键也可以约束并引用一组列。和以前一样，它需要以表约束形式编写。以下是一个虚构的语法示例：
A foreign key can also constrain and reference a group of columns. As usual, it then needs to be written in table constraint form. Here is a contrived syntax example:
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);当然，受约束列的数目和类型需要与引用列的数目和类型匹配。
Of course, the number and type of the constrained columns need to match the number and type of the referenced columns.
有时将外键约束的“另一张表”设为同表是很有用的；这称为 self-referential 外键。例如，如果您希望表的行表示树结构的节点，您可以写成
Sometimes it is useful for the “other table” of a foreign key constraint to be the same table; this is called a self-referential foreign key. For example, if you want rows of a table to represent nodes of a tree structure, you could write
CREATE TABLE tree (
node_id integer PRIMARY KEY,
parent_id integer REFERENCES tree,
name text,
...
);顶级节点将为 NULL parent_id，而非 NULL parent_id 条目将受到约束，以引用表中的有效行。
A top-level node would have NULL parent_id, while non-NULL parent_id entries would be constrained to reference valid rows of the table.
一张表可以有多个外键约束。这用于在表之间实现多对多关系。假设您有关于产品和订单的表，但现在您希望允许一个订单包含尽可能多的产品（上面的结构不允许）。您可以使用此表结构：
A table can have more than one foreign key constraint. This is used to implement many-to-many relationships between tables. Say you have tables about products and orders, but now you want to allow one order to contain possibly many products (which the structure above did not allow). You could use this table structure:
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)
);请注意，主键与最后一列中的外键重叠。
Notice that the primary key overlaps with the foreign keys in the last table.
我们知道，外键不允许创建与任何产品无关的订单。但是，如果在创建引用它的订单后删除了产品，会怎么样？SQL 也允许您处理这种情况。直观地说，我们有几个选择：
We know that the foreign keys disallow creation of orders that do not relate to any products. But what if a product is removed after an order is created that references it? SQL allows you to handle that as well. Intuitively, we have a few options:
为了说明这一点，让我们在上面多对多关系示例中实现以下策略：当有人想要移除仍然被订单引用的产品时（通过 order_items），我们会不允许这样做。如果有人移除订单，订单项也会被移除：
To illustrate this, let’s implement the following policy on the many-to-many relationship example above: when someone wants to remove a product that is still referenced by an order (via order_items), we disallow it. If someone removes an order, the order items are removed as well:
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 但默认值不满足外键约束，操作将失败。
Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key constraint, the operation will fail.
ON DELETE 操作的适当选择取决于相关表表示的对象类型。当引用表表示由引用表表示的内容的组件并且不能独立存在时，CASCADE 可能合适。如果两个表表示独立的对象，则 RESTRICT 或 NO ACTION 更合适；实际想要删除两个对象的应用程序必须明确指定这一点并运行两个删除命令。在上面的示例中，订单项是订单的一部分，如果在删除订单时自动删除订单项，将会很方便。但是，产品和订单是不同的东西，因此使删除产品自动导致删除一些订单项可能会被认为是有问题的。如果外键关系表示可选信息，则 SET NULL 或 SET DEFAULT 操作可能合适。例如，如果产品表中包含对产品经理的引用，并且产品经理条目被删除，那么将产品的产品经理设为 null 或默认值可能很有用。
The appropriate choice of ON DELETE action depends on what kinds of objects the related tables represent. When the referencing table represents something that is a component of what is represented by the referenced table and cannot exist independently, then CASCADE could be appropriate. If the two tables represent independent objects, then RESTRICT or NO ACTION is more appropriate; an application that actually wants to delete both objects would then have to be explicit about this and run two delete commands. In the above example, order items are part of an order, and it is convenient if they are deleted automatically if an order is deleted. But products and orders are different things, and so making a deletion of a product automatically cause the deletion of some order items could be considered problematic. The actions SET NULL or SET DEFAULT can be appropriate if a foreign-key relationship represents optional information. For example, if the products table contained a reference to a product manager, and the product manager entry gets deleted, then setting the product’s product manager to null or a default might be useful.
动作 SET NULL 和 SET DEFAULT 可以采用列列表来指定要设置哪些列。通常，所有外键约束的列都是设置的；在某些特殊情况下，仅设置子集是有用的。考虑以下示例：
The actions SET NULL and SET DEFAULT can take a column list to specify which columns to set. Normally, all columns of the foreign-key constraint are set; setting only a subset is useful in some special cases. Consider the following example:
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，但该列仍然是主键的一部分。
Without the specification of the column, the foreign key would also set the column tenant_id to null, but that column is still required as part of the primary key.
类似于 ON DELETE 也存在 ON UPDATE，它在引用列更改（更新）时调用。可能的动作相同，但不能为 SET NULL 和 SET DEFAULT 指定列列表。在这种情况下，CASCADE 表示应该将引用列的更新值复制到引用行中。
Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same, except that column lists cannot be specified for SET NULL and SET DEFAULT. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s).
通常，如果引用行的任何引用列为 null，则引用行不必满足外键约束。如果将 MATCH FULL 添加到外键声明中，引用行只有在其所有引用列都为 null 时才逃逸满足约束（因此，null 值和非 null 值的组合肯定会失败 MATCH FULL 约束）。如果您不希望引用行能够逃避满足外键约束的要求，请将引用列声明为 NOT NULL。
Normally, a referencing row need not satisfy the foreign key constraint if any of its referencing columns are null. If MATCH FULL is added to the foreign key declaration, a referencing row escapes satisfying the constraint only if all its referencing columns are null (so a mix of null and non-null values is guaranteed to fail a MATCH FULL constraint). If you don’t want referencing rows to be able to avoid satisfying the foreign key constraint, declare the referencing column(s) as NOT NULL.
外键必须引用既是主键或形成唯一约束的列，或者是来自非部分唯一索引的列。这意味着引用列始终具有索引以允许有效地查找引用行是否具有匹配项。由于 DELETE 来自引用表中的行或 UPDATE 来自引用列需要扫描引用表以查找与旧值匹配的行，因此通常对引用列编制索引也是一个好主意。由于并不总是需要这么做，并且在如何编制索引方面有很多可用的选择，所以外键约束的声明不会自动在引用列上创建索引。
A foreign key must reference columns that either are a primary key or form a unique constraint, or are columns from a non-partial unique index. This means that the referenced columns always have an index to allow efficient lookups on whether a referencing row has a match. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, the declaration of a foreign key constraint does not automatically create an index on the referencing columns.
有关更新和删除数据，更多信息请参见 Chapter 6 。另请参阅 CREATE TABLE 的参考文档中的外键约束语法的说明。
More information about updating and deleting data is in Chapter 6. Also see the description of foreign key constraint syntax in the reference documentation for CREATE TABLE.
5.4.6. Exclusion Constraints #
排除约束确保了如果使用指定的操作符在指定列或表达式上对任意两行进行比较，则至少一个这些操作符比较将返回假或空值。语法：
Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. The syntax is:
CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH &&)
);还可参阅 CREATE TABLE … CONSTRAINT … EXCLUDE 以获取详细信息。
See also CREATE TABLE … CONSTRAINT … EXCLUDE for details.
添加排除约束将自动创建一个类型为约束声明中指定的索引。
Adding an exclusion constraint will automatically create an index of the type specified in the constraint declaration.