{"Entry":{"collection":"sql","key":"set-constraints","name":"SET CONSTRAINTS","aliases":["set-constraints"],"metadata":{"aliases":["set-constraints"],"changed_in":["7.4"],"changes":[{"from":"7.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["notes"],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }"],"removed":["SET CONSTRAINTS { ALL | constraint [, ...] } { DEFERRED | IMMEDIATE }"]},"to":"7.4"},{"from":"7.4","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","notes","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.4","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","notes","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"9.0"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.4"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"10"}],"content_hash":"c978ae0cc7544301981cae0ce4c9e0a5326c9fe2898c19998f8a180c9dade4d0","derived_content_hash":"ffe356f73d8b619b7f9fc72ecc914a1190a96ff1d9d85187dc5e87805b558d83","editorial":{},"first_version":"7.1","group":"transaction","imported_at":"2026-09-27T17:57:27.309883+08:00","last_version":"20","name":"SET CONSTRAINTS","object":"CONSTRAINTS","position":13007,"present_in":["7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"set constraint check timing for the current transaction","purpose_zh":"","related":[],"repair_provenance":{"original_source_revision":"wiki-repair-20261023:57ea1a46ff18112df42d0775954ff1c06936df8912c6b90532b039d50c73e419","receipt_sha256":"80c94db40455a09eba780b48e1abddb4e7a5802190bd1b4d6a3cc51b5ba1a267","repair":"wiki-repair-20261023"},"slug":"set-constraints","source_rev":"wiki-repair-20261023:fd258c9584b84c2195e9862723a302513cc4184df9fe8c173482ce1fd88b45ef","synopsis":"SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }","verb":"SET"}},"Definition":{"Collection":"sql","Key":"set-constraints","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"set-constraints","SourceRevision":"wiki-repair-20261023:fd258c9584b84c2195e9862723a302513cc4184df9fe8c173482ce1fd88b45ef","Facts":{"anchor":"SQL-SET-CONSTRAINTS","description_zh":"为当前事务设置约束检查时机","description_zh_html":"\u003cpre\u003eSET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\n\u003ccode class=\"command\"\u003eSET CONSTRAINTS\u003c/code\u003e 设置当前事务中约束检查的行为。\u003ccode class=\"literal\"\u003eIMMEDIATE\u003c/code\u003e 约束会在每条语句结束时检查。\u003ccode class=\"literal\"\u003eDEFERRED\u003c/code\u003e 约束要等到事务提交时才检查。每个约束都有各自的 \u003ccode class=\"literal\"\u003eIMMEDIATE\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eDEFERRED\u003c/code\u003e 模式。\n  \u003c/p\u003e\u003cp\u003e\n   创建约束时，它会被赋予以下三种特性之一：\u003ccode class=\"literal\"\u003eDEFERRABLE INITIALLY DEFERRED\u003c/code\u003e、\u003ccode class=\"literal\"\u003eDEFERRABLE INITIALLY IMMEDIATE\u003c/code\u003e 或者\n   \u003ccode class=\"literal\"\u003eNOT DEFERRABLE\u003c/code\u003e。第三类始终是\n   \u003ccode class=\"literal\"\u003eIMMEDIATE\u003c/code\u003e，不会受到\n   \u003ccode class=\"command\"\u003eSET CONSTRAINTS\u003c/code\u003e 命令的影响。前两类在每个事务开始时都处于其指定的模式，但其行为可以在事务中通过\n   \u003ccode class=\"command\"\u003eSET CONSTRAINTS\u003c/code\u003e 修改。\n  \u003c/p\u003e\u003cp\u003e\n   带约束名称列表的 \u003ccode class=\"command\"\u003eSET CONSTRAINTS\u003c/code\u003e 只会修改这些约束的模式（它们都必须是可延迟的）。每个约束名称都可以带模式限定。如果未指定模式名称，就会使用当前模式搜索路径查找第一个匹配的名称。\u003ccode class=\"command\"\u003eSET CONSTRAINTS ALL\u003c/code\u003e 会修改所有可延迟约束的模式。\n  \u003c/p\u003e\u003cp\u003e\n   当 \u003ccode class=\"command\"\u003eSET CONSTRAINTS\u003c/code\u003e 将某个约束的模式从\n   \u003ccode class=\"literal\"\u003eDEFERRED\u003c/code\u003e 改为 \u003ccode class=\"literal\"\u003eIMMEDIATE\u003c/code\u003e 时，新模式具有追溯效力：任何原本会在事务结束时才检查的未决数据修改，都会改为在执行 \u003ccode class=\"command\"\u003eSET CONSTRAINTS\u003c/code\u003e 命令期间检查。如果违反了任何此类约束，\u003ccode class=\"command\"\u003eSET CONSTRAINTS\u003c/code\u003e 就会失败（并且不会改变该约束的模式）。因此，可以利用 \u003ccode class=\"command\"\u003eSET\n   CONSTRAINTS\u003c/code\u003e 强制在事务中的特定点执行约束检查。\n  \u003c/p\u003e\u003cp\u003e\n   当前，只有 \u003ccode class=\"literal\"\u003eUNIQUE\u003c/code\u003e、\u003ccode class=\"literal\"\u003ePRIMARY KEY\u003c/code\u003e、\u003ccode class=\"literal\"\u003eREFERENCES\u003c/code\u003e（外键）以及 \u003ccode class=\"literal\"\u003eEXCLUDE\u003c/code\u003e\n   约束受到这个设置的影响。\u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e 和 \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e 约束总是在一行被插入或修改时立即检查（\u003cspan class=\"emphasis\"\u003e\u003cem\u003e不是\u003c/em\u003e\u003c/span\u003e在语句结束时）。未声明为 \u003ccode class=\"literal\"\u003eDEFERRABLE\u003c/code\u003e 的唯一约束和排他约束也会立即检查。\n  \u003c/p\u003e\u003cp\u003e\n   被声明为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e约束触发器\u003c/span\u003e”\u003c/span\u003e的触发器，其引发也受此设置控制\n   — 它们会在相关约束应当被检查的同时引发。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e\n   因为 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 并不要求约束名称在同一模式内唯一（只要求在每个表内唯一），所以指定的约束名称有可能匹配到多个约束。在这种情况下，\u003ccode class=\"command\"\u003eSET CONSTRAINTS\u003c/code\u003e 会作用于所有匹配项。对于未带模式限定的名称，一旦在搜索路径中的某个模式里找到一个或多个匹配项，就不会再搜索路径中更靠后的模式。\n  \u003c/p\u003e\u003cp\u003e\n   这个命令只会改变当前事务中约束的行为。在事务块之外发出该命令会产生一条警告，除此之外不会有任何效果。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\n   这个命令符合 SQL 标准定义的行为，但有一个限制：在\n   \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 中，它不会应用在\n   \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e 和 \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e 约束上。此外，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 会立即检查不可延迟的唯一约束，而不是像标准所暗示的那样在语句结束时检查。\n  \u003c/p\u003e\u003c/section\u003e","file":"sql-set-constraints.html","lang":"en","name":"SET CONSTRAINTS","purpose":"set constraint check timing for the current transaction","purpose_zh":"为当前事务设置约束检查时机","related":[],"sections":[],"sections_same_as":"10","slug":"18","synopsis_html":"SET CONSTRAINTS { ALL | \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [, ...] } { DEFERRED | IMMEDIATE }","synopsis_text":"SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }","translation_provenance":{"inputs":[{"kind":"current-pgdoc-translation","path":"/Users/vonng/pgsty/pgdoc/zh/18/html/sql-set-constraints.html","sha256":"f1a8be9efa606bd587e6c7861dc892c68165daabecb17d9f6aa917d50cdf7abf"}],"kind":"same-version-pgdoc","locale":"zh-Hans","source_revision":"b1b500221dbe5437cfca004d6b04d3f6e422765d","version":"18"},"zh_from":"same-version-pgdoc"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"set-constraints","SourceDatabase":"center","Version":"18","Locale":"en","Title":"SET CONSTRAINTS","Summary":"set constraint check timing for the current transaction","BodyHTML":"\u003cpre\u003eSET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }\u003c/pre\u003e\u003csection\u003e\u003ch2\u003eDescription\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e sets the behavior of constraint checking within the current transaction. \u003ccode\u003eIMMEDIATE\u003c/code\u003e constraints are checked at the end of each statement. \u003ccode\u003eDEFERRED\u003c/code\u003e constraints are not checked until transaction commit. Each constraint has its own \u003ccode\u003eIMMEDIATE\u003c/code\u003e or \u003ccode\u003eDEFERRED\u003c/code\u003e mode.\u003c/p\u003e\u003cp\u003eUpon creation, a constraint is given one of three characteristics: \u003ccode\u003eDEFERRABLE INITIALLY DEFERRED\u003c/code\u003e, \u003ccode\u003eDEFERRABLE INITIALLY IMMEDIATE\u003c/code\u003e, or \u003ccode\u003eNOT DEFERRABLE\u003c/code\u003e. The third class is always \u003ccode\u003eIMMEDIATE\u003c/code\u003e and is not affected by the \u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e command. The first two classes start every transaction in the indicated mode, but their behavior can be changed within a transaction by \u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e.\u003c/p\u003e\u003cp\u003e\u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e 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. \u003ccode\u003eSET CONSTRAINTS ALL\u003c/code\u003e changes the mode of all deferrable constraints.\u003c/p\u003e\u003cp\u003eWhen \u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e changes the mode of a constraint from \u003ccode\u003eDEFERRED\u003c/code\u003e to \u003ccode\u003eIMMEDIATE\u003c/code\u003e, 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 \u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e command. If any such constraint is violated, the \u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e fails (and does not change the constraint mode). Thus, \u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e can be used to force checking of constraints to occur at a specific point in a transaction.\u003c/p\u003e\u003cp\u003eCurrently, only \u003ccode\u003eUNIQUE\u003c/code\u003e, \u003ccode\u003ePRIMARY KEY\u003c/code\u003e, \u003ccode\u003eREFERENCES\u003c/code\u003e (foreign key), and \u003ccode\u003eEXCLUDE\u003c/code\u003e constraints are affected by this setting. \u003ccode\u003eNOT NULL\u003c/code\u003e and \u003ccode\u003eCHECK\u003c/code\u003e constraints are always checked immediately when a row is inserted or modified (\u003cspan\u003e\u003cem\u003enot\u003c/em\u003e\u003c/span\u003e at the end of the statement). Uniqueness and exclusion constraints that have not been declared \u003ccode\u003eDEFERRABLE\u003c/code\u003e are also checked immediately.\u003c/p\u003e\u003cp\u003eThe firing of triggers that are declared as \u003cspan\u003e“\u003cspan\u003econstraint triggers\u003c/span\u003e”\u003c/span\u003e is also controlled by this setting — they fire at the same time that the associated constraint should be checked.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eNotes\u003c/h2\u003e\u003cp\u003eBecause \u003cspan\u003ePostgreSQL\u003c/span\u003e 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 \u003ccode\u003eSET CONSTRAINTS\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003eThis 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.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eCompatibility\u003c/h2\u003e\u003cp\u003eThis command complies with the behavior defined in the SQL standard, except for the limitation that, in \u003cspan\u003ePostgreSQL\u003c/span\u003e, it does not apply to \u003ccode\u003eNOT NULL\u003c/code\u003e and \u003ccode\u003eCHECK\u003c/code\u003e constraints. Also, \u003cspan\u003ePostgreSQL\u003c/span\u003e checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest.\u003c/p\u003e\u003c/section\u003e","SourceRevision":"wiki-repair-20261023:fd258c9584b84c2195e9862723a302513cc4184df9fe8c173482ce1fd88b45ef","ContentHash":"ff414d9cfee404c124aa71c2b2b09ecde3e7f67022b4be580e907193ae96d61d","Payload":{"related":[],"sections":[],"sections_same_as":"10","synopsis_html":"SET CONSTRAINTS { ALL | \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [, ...] } { DEFERRED | IMMEDIATE }","synopsis_text":"SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }"}},"RequestedLocale":"en","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}