Postgresql 中文操作指南
SET CONSTRAINTS
SET CONSTRAINTS — 为当前事务设置约束检查时间
SET CONSTRAINTS — set constraint check timing for the current transaction
Description
SET CONSTRAINTS 设置当前事务中约束检查的行为。 IMMEDIATE 约束在每个语句末尾进行检查。 DEFERRED 约束在事务提交之前不会进行检查。每个约束都有自己的 IMMEDIATE 或 DEFERRED 模式。
SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.
在创建时，约束将获得以下三个特征之一： DEFERRABLE INITIALLY DEFERRED 、 DEFERRABLE INITIALLY IMMEDIATE 或 NOT DEFERRABLE 。第三类始终为 IMMEDIATE ，不会受 SET CONSTRAINTS 命令影响。前两类以指示的模式启动每个事务，但其行为可以在事务中通过 SET CONSTRAINTS 进行更改。
Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. The third class is always IMMEDIATE and is not affected by the SET CONSTRAINTS command. The first two classes start every transaction in the indicated mode, but their behavior can be changed within a transaction by SET CONSTRAINTS.
SET CONSTRAINTS 使用约束名称列表仅更改那些约束的模式（这些约束必须全部是可延迟的）。每个约束名称都可以限定架构。如果没有指定架构名称，则使用当前架构搜索路径来查找第一个匹配的名称。 SET CONSTRAINTS ALL 更改所有可延迟约束的模式。
SET CONSTRAINTS with a list of constraint names changes the mode of just those constraints (which must all be deferrable). Each constraint name can be schema-qualified. The current schema search path is used to find the first matching name if no schema name is specified. SET CONSTRAINTS ALL changes the mode of all deferrable constraints.
当 SET CONSTRAINTS 将约束的模式从 DEFERRED 更改为 IMMEDIATE 时，新模式会产生追溯效应：在事务末尾才会检查的任何未完成数据修改反而会在执行 SET CONSTRAINTS 命令期间进行检查。如果违反了任何此类约束， SET CONSTRAINTS 将会失败（且不会更改约束模式）。因此， SET CONSTRAINTS 可以用来强制在事务中的特定点检查约束。
When SET CONSTRAINTS changes the mode of a constraint from DEFERRED to IMMEDIATE, the new mode takes effect retroactively: any outstanding data modifications that would have been checked at the end of the transaction are instead checked during the execution of the SET CONSTRAINTS command. If any such constraint is violated, the SET CONSTRAINTS fails (and does not change the constraint mode). Thus, SET CONSTRAINTS can be used to force checking of constraints to occur at a specific point in a transaction.
目前，只有 UNIQUE 、 PRIMARY KEY 、 REFERENCES （外键）和 EXCLUDE 约束会受此设置影响。在插入或修改行时，总是会立即检查 NOT NULL 和 CHECK 约束（在语句末尾检查 not ）。没有声明为 DEFERRABLE 的唯一性约束和排他约束也立即进行检查。
Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.
声明为“约束触发器”的触发器的触发也受此设置控制 — 约束应在触发器触发同时进行检查。
The firing of triggers that are declared as “constraint triggers” is also controlled by this setting — they fire at the same time that the associated constraint should be checked.
Notes
由于 PostgreSQL 并未要求约束名称在架构内保持唯一（仅每张表一个），因此指定一个约束名称可能有多个匹配项。在这种情况下， SET CONSTRAINTS 会对所有匹配项采取操作。对于非架构限定名称，一旦在搜索路径中的某些架构中找到了匹配项，就不会搜索路径中后面出现的架构。
Because PostgreSQL does not require constraint names to be unique within a schema (but only per-table), it is possible that there is more than one match for a specified constraint name. In this case SET CONSTRAINTS will act on all matches. For a non-schema-qualified name, once a match or matches have been found in some schema in the search path, schemas appearing later in the path are not searched.
此命令仅更改当前事务中约束的行为。在事务块外部发布此命令会发出警告，否则没有任何效果。
This command only alters the behavior of constraints within the current transaction. Issuing this outside of a transaction block emits a warning and otherwise has no effect.
Compatibility
此命令符合 SQL 标准中定义的行为，但有一个限制例外，即在 PostgreSQL 中，它不适用于 NOT NULL 和 CHECK 约束。此外，PostgreSQL 会立即检查不可延迟的唯一性约束，而非在语句末尾进行检查，就像标准建议的那样。
This command complies with the behavior defined in the SQL standard, except for the limitation that, in PostgreSQL, it does not apply to NOT NULL and CHECK constraints. Also, PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest.