Linux and UNIX Man Pages

Linux & Unix Commands - Search Man Pages

set_constraints(7) [centos man page]

SET 
CONSTRAINTS(7) PostgreSQL 9.2.7 Documentation SET CONSTRAINTS(7) NAME
SET_CONSTRAINTS - set constraint check timing for the current transaction SYNOPSIS
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE } DESCRIPTION
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. 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 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. 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. 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
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. Thus, if you execute this command outside of a transaction block (BEGIN/COMMIT pair), it will not appear to have any effect. COMPATIBILITY
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. PostgreSQL 9.2.7 2014-02-17 SET CONSTRAINTS(7)

Check Out this Related Man Page

SET 
CONSTRAINTS(7) SQL Commands SET CONSTRAINTS(7) NAME
SET CONSTRAINTS - set the constraint mode of the current transaction SYNOPSIS
SET CONSTRAINTS { ALL | constraint [, ...] } { DEFERRED | IMMEDIATE } DESCRIPTION
SET CONSTRAINTS sets the behavior of constraint evaluation in the current transaction. In IMMEDIATE mode, constraints are checked at the end of each statement. In DEFERRED mode, constraints are not checked until transaction commit. Note: This command only alters the behavior of constraints within the current transaction. Thus, if you execute this command outside of an explicit transaction block (such as one started with BEGIN), it will not appear to have any effect. If you wish to change the behavior of a constraint without needing to issue a SET CONSTRAINTS command in every transaction, specify INITIALLY DEFERRED or INI- TIALLY IMMEDIATE when you create the constraint. When you change the mode of a constraint to be IMMEDIATE , the new constraint mode takes effect retroactively: any outstanding data modifi- cations that would have been checked at the end of the transaction (when using DEFERRED) are instead checked during the execution of the SET CONSTRAINTS command. Upon creation, a constraint is always give one of three characteristics: INITIALLY DEFERRED, INITIALLY IMMEDIATE DEFERRABLE, or INITIALLY IMMEDIATE NOT DEFERRABLE. The third class is not affected by the SET CONSTRAINTS command. Currently, only foreign key constraints are affected by this setting. Check and unique constraints are always effectively initially immedi- ate not deferrable. COMPATIBILITY
SQL92, SQL99 SET CONSTRAINTS is defined in SQL92 and SQL99. The implementation in PostgreSQL complies with the behavior defined in the standard, except for the PostgreSQL limitation that SET CONSTRAINTS cannot be applied to check or unique constraints. SQL - Language Statements 2002-11-22 SET CONSTRAINTS(7)
Man Page

Featured Tech Videos