Set Constraints -- Fields and controls

Off

On without checking

Referential constraints

Check constraints

On with checking

Exception table schema

Exception table name

OK

Show SQL

Cancel

Off

Select this radio button to turn off foreign key and check constraints for a table and place the table into check pending state. If the table is already in check pending state for one of the constraint types, the other constraint type is turned off also.

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

On without checking

Select this radio button to specify that the table not be checked immediately for constraint violations when constraint checking is turned on.

When you select this radio button, you can use the Referential constraints and Check constraints check boxes to specify the constraint type for which you are turning on constraint checking. (Referential constraints are foreign keys).

When you turn on constraint checking without performing the checking, this fact is recorded in the database catalog (the value in the CONST_CHECKED column in the SYSCAT.TABLES view is set to 'U'). This value indicates that you have assumed responsibility (over the database management system) for ensuring the integrity of the data, with respect to the constraints turned on. This value remains the same until either:

• The table is put back into check pending state (by selecting the Off radio button in the Set Constraints window).

• All unchecked constraints for the table are dropped.

Referential constraints

Select this check box to specify that foreign key constraints be turned on.

This check box is available only when the On without checking radio button is selected.

If this table is a parent table, the check pending states of its dependent and descendent tables are unchanged.

If this table is a dependent table and its parent table is also in the check pending state, you cannot turn on referential constraints for this table. However, you can turn on check constraints regardless of whether the parent table is in check pending state.

Check constraints

Select this check box to specify that check constraints be turned on.

This check box is available only when the On without checking radio button is selected.

On with checking

Select this radio button to turn on constraint checking for both referential (foreign key) and check constraints, and to check the table immediately for constraint violations.

If you select this radio button, you can specify an exception table. If you specify an exception table, any row that is in violation of a foreign key or check constraint is deleted from your table and collected in the exception table. The constraints are turned back on again and the table is taken out of the check pending state, regardless of whether errors are detected. A warning (SQLSTATE 01603) is issued to indicate that one or more rows were moved to the exception table.

If you do not specify an exception table and any constraints are violated, only the first violation detected is returned to you. The table is left in the check pending state.

Exception table schema

Use this box to specify the name of the schema for the exception table. This box initially contains the user name under which you are currently connected to the database. To specify a different schema name, use one of these methods:

• Click on the down arrow to display a list of existing schemas and select one.

• Type the name of a schema in the box. The schema must exist. The name can be an ordinary or delimited identifier.

This box is available only when the On with checking radio button is selected.

Exception table name

Use this box to specify the name of the exception table. Use one of these methods:

• Click on the down arrow to display a list of existing tables owned by the schema specified in the Exception table schema box.

• Type the name of a table in the box. The table must exist.

This box is available only when the On with checking radio button is selected.