Checking for constraint violations using SET INTEGRITY - IBM DB2 9.7 for Linux, UNIX, and Windows

Typically, you need to manually perform integrity processing for a table in three situations: After loading data into a table; when altering a table by adding constraints on the table; and when altering a table to add a generated column.

Before you begin

To turn on constraint checking for a table and performing integrity processing on the table, you need one of the following:
- CONTROL privileges on the tables being checked, and if exceptions are being posted to one or more tables, INSERT privilege on the exception tables
- CONTROL privilege on all descendent foreign key tables, descendent immediate materialized query tables, and descendent immediate staging tables that will implicitly be placed in the Set Integrity Pending state by the statement
- LOAD authority, and if exceptions are being posted to one or more tables:
  - SELECT and DELETE privilege on each table being checked
  - INSERT privilege on the exception tables
To turn on constraint checking for a table without performing integrity processing on the table, you need one of the following:
- CONTROL privileges on the tables being checked
- CONTROL privilege on each descendent foreign key table, descendent immediate materialized query table, and descendent immediate staging table that will implicitly be placed in the Set Integrity Pending state by the statement
- LOAD authority
- DATAACCESS authority
- DBADM authority
To turn off constraint checking, immediate refreshing, or immediate propagation for tables, you need one of the following:
- CONTROL privilege on the table, and on all descendent foreign key tables, descendent immediate materialized query tables, and descendent immediate staging tables that will have their integrity checking turned off by the statement
- LOAD authority

About this task

The load operation causes a table to be put into Set Integrity Pending state automatically if the table has constraints defined on it or if it has dependent foreign key tables, dependent materialized query tables, or dependent staging tables. When the load operation is completed, you can verify the integrity of the loaded data and you can turn on constraint checking for the table. If the table has dependent foreign key tables, dependent materialized query tables, or dependent staging tables, they will be automatically put into Set Integrity Pending state. You will need to use the Set Integrity window to perform separate integrity processing on each of these tables.

If you are altering a table by adding a foreign key, a check constraint or a generated column, you need to turn off constraint checking before you alter the table. After you add the constraint, you need to check the existing data for violations to the newly added constraint and you need to turn constraint checking back on. In addition, if you are loading data into the table, you cannot activate constraint checking on the table until you complete loading data into it. If you are importing data into the table, you should activate constraint checking on the table before you import data into it.

Constraints checking refers to checking for constraints violations, foreign key violations, and generated columns violations. Integrity processing refers to populating identity and generated columns, refreshing materialized query tables, and propagating to staging tables, in addition to performing constraints checking.

Normally, referential integrity and check constraints on a table are automatically enforced, materialized query tables are automatically refreshed immediately, and staging tables are automatically propagated. In some situations, you might need to manually change this behavior.

To check for constraint violations using the command line, use the SET INTEGRITY statement.

Procedure

To check for constraint violations using the Control Center:

Open the Set Integrity window: From the Control Center, expand the object tree until you find the Tables folder. Click on the Tables folder. Any existing tables are displayed in the pane on the right side of the window. Right-click the table you want and select Set Integrity from the pop-up menu. The Set Integrity window opens.
Review the Current Integrity Status of the table you are working with.
To turn on constraint checking for a table and not check the table data:
1. Select the Immediate and unchecked radio button.
2. Specify the type of integrity processing that you are turning on.
3. Select the Full Access radio button to immediately perform data movement operations against the table (such as reorganize or redistribute). However, note that subsequent refreshes of dependent materialized query tables will take longer. If the table has an associated materialized query table, it is recommended that you do not select this radio button in order to reduce the time needed to refresh the materialized query table.
To turn on constraint checking for a table and check the existing table data:
1. Select the Immediate and checked radio button.
2. Select which type of integrity processing that you want to perform. If the Current integrity status shows that the constraints checked value for the materialized query table is incomplete, you cannot incrementally refresh the materialized query table.
3. Optional: If you want identity or generated columns to be populated during integrity processing, select the Force generated check box.
4. If the table is not a staging table, make sure that the Prune check box is unchecked.
5. Select the Full Access radio button to immediately perform data movement operations against the table.
6. Optional: Specify an exception table. Any row that is in violation of a referential or check constraint will be deleted from your table and copied to the exception table. If you do not specify an exception table, when a constraint is violated, only the first violation detected is returned to you and the table is left in the Set Integrity Pending state.
To turn off constraint checking, immediate refreshing, or immediate propagation for a table:
1. Select the Off radio button. The table will be put in Set Integrity Pending state.
2. Use the Cascade option to specify whether you want to cascade immediately or defer cascading. If you are cascading immediately, use the Materialized Query Tables, Foreign Key Tables, and Staging Tables check boxes to indicate the tables to which you want to cascade. If you turn off constraint checking for a parent table and specify that you want to cascade the changes to foreign key tables, the foreign key constraints of all of its descendent foreign key tables are also turned off. If you turn off constraint checking for a underlying table and specify that you want to cascade the check pending state to materialized query tables, the refresh immediate properties of all its dependent materialized query tables are also turned off. If you turn off constraint checking for a underlying table and specify that you want to cascade the Set Integrity Pending state to staging tables the propagate immediate properties of all its dependent staging tables are also turned off.

Example

Troubleshooting tip

Symptom

You receive the following error message when you try to turn on constraints checking, immediate refresh, or immediate propagation for a table:

DB2® Message: Cannot check a dependent table TABLE1 using the SET INTEGRITY statement while the parent table or underlying table TABLE2 is in the Set Integrity Pending state or if it will be put into the Set Integrity Pending state by the SET INTEGRITY statement.
Where TABLE1 is the table for which you are trying to turn on constraints checking, immediate refresh, or immediate propagation and it is dependent on TABLE2.

Possible cause

Constraint checking, immediate refresh, or immediate propagation cannot be turned on for a table that has a parent or underlying table in Set Integrity Pending.

Action

Bring the parent or underlying table out of Set Integrity Pending by turning on constraint checking for the table. Begin with the table identified as the parent or underlying table in the DB2 message. If that table is dependent on another table, you need to turn on constraint checking in a top-down approach from the table at the top of the dependency chain.

Attention: If the selected table has a cyclical referential constraint relationship with one or more tables, you cannot use the Set Integrity window to turn on constraint checking. In this case, you must use the Command Editor to issue the SQL SET INTEGRITY statement.