SQL Reference

SET CONSTRAINTS

The SET CONSTRAINTS statement is used to do one of the following:

Turn off check constraint and referential constraint checking for one or more tables. If the table is a summary table with REFRESH IMMEDIATE, the immediate refreshing of the data is turned off. Note that this places the table(s) into a check pending state where only limited access by a restricted set of statements and commands is allowed. Primary key and unique constraints continue to be checked.
Both turn the constraint checking back on for one or more tables and to carry out all the deferred checking. If the table is a summary table, the data is refreshed as necessary and, when defined with the REFRESH IMMEDIATE attribute, immediate refreshing of the data is turned on.
Turn on referential constraints and/or check constraints for one or more tables without first carrying out any deferred constraint checking. If the table is a summary table defined with the REFRESH IMMEDIATE attribute, immediate refreshing of the data is turned on.
Place the table into Check pending state if the table is already in DataLink Reconcile Pending (DRP) or DataLink Reconcile Not Possible (DRNP) state. If a table is not in either of those states, then unconditionally set the table in DRP state and Check-pending state.

The SET CONSTRAINTS statement is under transaction control.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

The privileges required to execute SET CONSTRAINTS depend on the use of the statement, as outlined below:

Turn off constraint checking.
The privileges of the authorization ID of the statement must include at least one of the following:
- CONTROL privilege on the tables and all their dependents and descendants in referential integrity constraints.
- SYSADM or DBADM authority.
Both turn on constraints and carry out checking.
The privileges of the authorization ID of the statement must include at least one of the following:
- SYSADM or DBADM authority.
- CONTROL privilege on the tables that are being checked and if exceptions are being posted to one or more tables, INSERT privilege on the exception tables.
Turn on constraints without first carrying out checking.
The authorization ID of the statement must have at least one of the following:
- SYSADM or DBADM authority
- CONTROL privilege on the tables that are being checked.

Syntax

>>-SET--CONSTRAINTS--------------------------------------------->
 
             .-,-------------.
             V               |
>-----+-FOR-----table-name---+---+-OFF------------------------------------------+-+>
      |                          +-TO DATALINK RECONCILE PENDING----------------+ |
      |                          '-IMMEDIATE CHECKED--+-----------------------+-' |
      |                                               '-| exception-clause |--'   |
      |      .-,-------------------------------------.                            |
      |      V                                       |                            |
      '-FOR-----table-name--| constraint-options |---+---IMMEDIATE UNCHECKED------'
 
>--------------------------------------------------------------><
 
exception-clause
 
                      .-,----------------------------------.
                      V                                    |
|---FOR--EXCEPTION-------IN--table-name--USE--table-name---+----|
 
constraint-options
 
|---+-ALL-----------------------------------+-------------------|
    |  .-,--------------------------------. |
    |  V                                  | |
    '----+-----------------------------+--+-'
         +-FOREIGN KEY-----------------+
         +-CHECK-----------------------+
         +-DATALINK RECONCILE PENDING--+
         '-SUMMARY---------------------'

Description

table-name

Identifies a base table for which constraint checking is to be turned either on or off or a summary table for which immediate refreshing is to be turned either on or off. It must be a table described in the catalog and must not be a view, catalog table or typed table. Only one summary table may be specified in the statement (SQLSTATE 42997).

OFF

Specifies that the tables are to have their foreign key constraints and check constraints turned off and are, therefore to be placed into the check pending state. If it is a summary table, then immediate refreshing is turned off (if applicable) and the summary table is placed into check pending state.

Note that it is possible that a table may already be in the check pending state with only one type of constraint checking turned off; in such a situation the other type of constraint checking will also be turned off.

If any table in the list is a parent table, the check pending state for foreign key constraints is extended to all dependent and descendent tables.

If any table in the list is an underlying table of a summary table, the check pending state is extended to such summary tables.

Only very limited activity is allowed on a table that is in the check pending state. Notes lists the restrictions.

TO DATALINK RECONCILE PENDING

Specifies that the tables are to have DATALINK integrity constraint checking turned off and the tables placed in check pending state. If the table is already in DataLink Reconcile Not Possible (DRNP) state, it remains in this state with check pending. Otherwise, the table is set to DataLink Reconcile Pending (DRP) state.

Dependent and descendent table are not affected when this option is specified.

IMMEDIATE CHECKED

Specifies that the table is to have its constraints turned on and that the constraint checking that was deferred is to be carried out. This is done in accordance with the information set in the STATUS and CONST_CHECKED columns of the SYSCAT.TABLES catalog. That is:

The value in STATUS must be C (the table is in the check pending state) or an error (SQLSTATE 51027) is returned.
The value in CONST_CHECKED indicates which constraints are to be checked.

If it is a summary table, then the data is checked against the query and refreshed as necessary.

DATALINK values are not checked, even when the table is in DRP or DRNP state. The RECONCILE command or API should be used to perform the reconciliation of DATALINK values. The table will be taken out of check pending state but continue to have the DRP or DRNP flag set. This makes the table usable while the reconciliation of DATALINK values can be deferred to another time.

exception-clause

FOR EXCEPTION

Indicates that any row that is in violation of a foreign key constraint or a check constraint will be copied to an exception table and deleted from the original table. See Appendix N. Exception Tables for more information on these user-defined tables. Even if errors are detected the constraints are turned back on again and the table is taken out of the check pending state. A warning (SQLSTATE 01603) is issued to indicate that one or more rows have been moved to the exception tables.

If the FOR EXCEPTION clause is not specified and any constraints are violated, then only the first violation detected is returned to the user (SQLSTATE 23514). In the case of a violation in any table, all the tables are left in the check pending state, as they were before the execution of the statement. This clause cannot be specified if the table-name is a summary table (SQLSTATE 42997).

IN table-name

Specifies the table from which rows that violate constraints are to be copied. There must be one exception table specified for each table being checked.

USE table-name

Specifies the exception table into which error rows are to be copied.

constraint-options

Used to define the constraint options that are set to IMMEDIATE UNCHECKED.

ALL: This indicates that both foreign key constraints and check constraints are to be turned on.
FOREIGN KEY: This indicates that foreign key constraints are to be turned on.
CHECK: This indicates that check constraints are to be turned on.
DATALINK RECONCILE PENDING: This indicates that DATALINK integrity constraints are to be turned on.
SUMMARY: This indicates that the summary table should be refreshed and if the table has the REFRESH IMMEDIATE attribute, immediate refreshing should be turned on.

IMMEDIATE UNCHECKED

Specifies one of the following:

The table is to have its constraints turned on (and, thus, are to be taken out of the check pending state) without having the table checked for constraint violations or the summary table is to have immediate refreshing turned on and be taken out of check pending state.
This is specified for a given table either by specifying ALL, or by specifying CHECK when only check constraints are off for that table, or by specifying FOREIGN KEY when only foreign key constraints are off for that table, or by specifying DATALINK RECONCILE PENDING when only DATALINK integrity constraints are off for that table or by specifying SUMMARY when only summary table query checking is off for that summary table.
The table is to have one type of constraint turned on but is to be left in the check pending state.
This is specified for a given table by specifying only CHECK, FOREIGN KEY, SUMMARY or DATALINK RECONCILE PENDING when any of those types of constraints are off for that table.

The state change is not extended to any tables not explicitly included in the list.

If the parent of a dependent table is in the check pending state, the foreign key constraints of a dependent table cannot be marked to bypass checking (the check constraints checking can be bypassed).

The implications with respect to data integrity should be considered before using this option. See Notes.

Notes

Effects on tables in the check pending state:
- Use of SELECT, INSERT, UPDATE, or DELETE is disallowed on a table that is either:
  - in the check pending state itself
  - or requires access to another table that is in the check pending state.
  For example, a DELETE of a row in a parent table that cascades to a dependent table that is in the check pending state is not allowed.
- New constraints added to a table are normally enforced immediately. However, if the table is in check pending state the checking of any new constraints is deferred until the table is taken out of the check pending state.
- The CREATE INDEX statement cannot reference any tables that are in the check pending state. Similarly, ALTER TABLE to add a primary key or unique constraint cannot reference any tables that are in the check pending state.
- The utilities EXPORT, IMPORT, REORG, and REORGCHK are not allowed to operate on a table in the check pending state. Note that the IMPORT utility differs from the LOAD utility in that it always checks the constraints immediately.
- The utilities LOAD, BACKUP, RESTORE, ROLLFORWARD, UPDATE STATISTICS, RUNSTATS, LIST HISTORY, and ROLLFORWARD are allowed on a table in the check pending state.
- The statements ALTER TABLE, COMMENT ON, DROP TABLE, CREATE ALIAS, CREATE TRIGGER, CREATE VIEW, GRANT, REVOKE, and SET CONSTRAINTS can reference a table that is in the check pending state.
- Packages, views and any other objects that depend on a table that is in the check pending state will return an error when the table is accessed at run time.
The removal of violating rows by the SET CONSTRAINTS statement is not a delete event. Therefore, triggers are never activated by a SET CONSTRAINTS statement.
Warning about the use of the IMMEDIATE UNCHECKED clause:
This clause is intended to be used by utility programs and its use by application programs is not recommended.
The fact that constraints were turned on without doing deferred checking will be recorded in the catalog (the value in the CONST_CHECKED column in the SYSCAT.TABLES view will be set to 'U'). This indicates that the user has assumed responsibility for data integrity with respect to the specific constraints. This value remains until either:
- The table is put back into the check pending state (by referencing the table in a SET CONSTRAINTS statement with the OFF clause).
- All unchecked constraints for the table are dropped.
- A REFRESH TABLE statement is issued for a summary table.
A table that is in DataLink Reconcile Not Possible (DRNP) state requires corrective action to be taken (possibly outside of the database). Once corrective action is completed, the table is taken out of DRNP state using the IMMEDIATE UNCHECKED option. The RECONCILE command or API should then be used to check the DATALINK integrity constraints. For more details refer on removing a table from DataLink Reconcile Not Possible state refer to DataLinks Administration and Reference.
While constraints are being checked an exclusive lock is held on each table specified in the SET CONSTRAINTS invocation.
A shared lock on each table that is not listed in the SET CONSTRAINTS invocation but is a parent table of one of the dependent tables being checked.
If an error occurs during constraint checking, all the effects of the checking including deleting from the original and inserting into the exception tables will be rolled back.

Example

Example 1: The following is an example of a query that gives us information about the check pending state of tables. SUBSTR is used to extract the first 2 bytes of the CONST_CHECKED column of SYSCAT.TABLES. The first byte represents foreign key constraints, and the second byte represents check constraints.

   SELECT TABNAME,
     SUBSTR( CONST_CHECKED, 1, 1 ) AS FK_CHECKED,
     SUBSTR( CONST_CHECKED, 2, 1 ) AS CC_CHECKED
     FROM SYSCAT.TABLES
     WHERE STATUS = 'C';

Example 2: Set tables T1 and T2 in the check pending state:

   SET CONSTRAINTS FOR T1, T2 OFF;

Example 3: Check the constraints for T1 and get the first violation only:

   SET CONSTRAINTS FOR T1 IMMEDIATE CHECKED

Example 4: Check the constraints for T1 and T2 and put the violating rows into exception tables E1 and E2:

   SET CONSTRAINTS FOR T1, T2 IMMEDIATE CHECKED
      FOR EXCEPTION IN T1 USE E1, 
                    IN T2 USE E2;

Example 5: Enable FOREIGN KEY constraint checking in T1 and CHECK constraint checking in T2 to be bypassed with the IMMEDIATE CHECKED option:

   SET CONSTRAINTS FOR T1 FOREIGN KEY,
                               T2 CHECK IMMEDIATE UNCHECKED;

Example 6: Add a check constraint and a foreign key to the EMP_ACT table, using two ALTER TABLE statements. To perform constraint checking in a single pass of the table, constraint checking is turned off before the ALTER statements and checked after execution.

   SET CONSTRAINTS FOR EMP_ACT OFF;
   ALTER TABLE EMP_ACT ADD CHECK (EMSTDATE <= EMENDATE);
   ALTER TABLE EMP_ACT ADD FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE;
   SET CONSTRAINTS FOR EMP_ACT IMMEDIATE CHECKED;

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]