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.
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.
To check for constraint violations using the Control Center:
Troubleshooting tip
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.