Informix Guide to SQL: Syntax SQL Statements

Use the SET Database Object Mode statement to change the mode of constraints, indexes, and triggers.

To specify whether constraints are checked at the statement level or at the transaction level, see SET Transaction Mode.

When you change the mode of constraints, indexes, or triggers, the change is persistent. The setting remains in effect until you change the mode of the database object again.

The sysobjstate system catalog table lists all of the database objects in the database and the current mode of each database object. For information on the sysobjstate system catalog table, see the Informix Guide to SQL: Reference.

To change the mode of a constraint, index, or trigger, you must have the necessary privileges. Specifically, you must meet one of the following requirements:

• You must have the DBA privilege on the database.
• You must be the owner of the table on which the database object is defined and must have the Resource privilege on the database.
• You must have the Alter privilege on the table on which the database object is defined and the Resource privilege on the database.

Use the table-mode format to change the mode of all database objects of a given type that have been defined on a particular table.

For example, to disable all constraints that are defined on the cust_subset table, enter the following statement:

SET CONSTRAINTS FOR cust_subset DISABLED

When you use the table-mode format, you can change the modes of more than one database object type with a single SET Database Object Mode statement. For example, to enable all constraints, indexes, and triggers that are defined on the cust_subset table, enter the following statement:

SET CONSTRAINTS, INDEXES, TRIGGERS FOR cust_subset

	ENABLED

Use the list-mode format to change the mode for a particular constraint, index, or trigger.

For example, to change the mode of the unique index unq_ssn on the cust_subset table to filtering, enter the following statement:

SET INDEXES unq_ssn FILTERING

You can also use the list-mode format to change the mode for a list of constraints, indexes, or triggers that are defined on the same table. Assume that four triggers are defined on the cust_subset table: insert_trig, update_trig, delete_trig, and execute_trig. Also assume that all four triggers are enabled. To disable all triggers except execute_trig, enter the following statement:

SET TRIGGERS insert_trig, update_trig, delete_trig DISABLED

You can specify a disabled, enabled, or filtering mode for a constraint or a unique index.

If you do not specify the mode for a constraint in a CREATE TABLE, ALTER TABLE, or SET Database Object Mode statement, the constraint is enabled by default.

If you do not specify the mode for an index in the CREATE INDEX or SET Database Object Mode statement, the index is enabled by default.

You can use database object modes to control the effects of INSERT, DELETE, and UPDATE statements. Your choice of mode affects the tables whose data you are manipulating, the behavior of the database objects defined on those tables, and the behavior of the data manipulation statements themselves.

Constraints, indexes, and triggers are enabled by default. The CREATE TABLE, ALTER TABLE, CREATE INDEX, and CREATE TRIGGER statements create database objects in the enabled mode unless you specify another mode in the statement.

When a database object is enabled, the database server recognizes the existence of the database object and takes the database object into consideration while it executes an INSERT, DELETE, or UPDATE statement. Thus, an enabled constraint is enforced, an enabled index updated, and an enabled trigger is executed when the trigger event takes place. When you enable constraints and unique indexes, if a violating row exists, the data manipulation statement fails (that is no rows change) and the database server returns an error message.

When a database object is disabled, the database server does not take it into consideration during the execution of an INSERT, DELETE, or UPDATE statement. A disabled constraint is not enforced, a disabled index is not updated, and a disabled trigger is not executed when the trigger event takes place. When you disable constraints and unique indexes, any data manipulation statement that violates the restriction of the constraint or unique index succeeds, (that is the target row is changed) and the database server does not return an error message.

You can use the disabled mode to add a new constraint or new unique index to an existing table, even if some rows in the table do not satisfy the new integrity specification. For information on adding a new constraint, see Adding a Constraint When Existing Rows Violate the Constraintin the ALTER TABLE statement. For information on adding a new unique index, see Adding a Unique Index When Duplicate Values Exist in the Column in the CREATE INDEX statement.

When a constraint or unique index is in filtering mode, the INSERT, DELETE, or UPDATE statement succeeds, but the database server enforces the constraint or the unique-index requirement by writing any failed rows to the violations table associated with the target table. Diagnostic information about the constraint violation or unique-index violation is written to the diagnostics table associated with the target table.

Filtering mode has the following specific effects on INSERT, UPDATE, and DELETE statements:

• A constraint violation or unique-index violation during an INSERT statement causes the database server to make a copy of the nonconforming record and write it to the violations table. The database server does not write the nonconforming record to the target table. If the INSERT statement is not a singleton insert, the rest of the insert operation proceeds with the next record.
• A constraint violation or unique-index violation during an UPDATE statement causes the database server to make a copy of the existing record that was to be updated and write it to the violations table. The database server also makes a copy of the new record and writes it to the violations table. The database server does not update the actual record in the target table. If the UPDATE statement is not a singleton update, the rest of the update operation proceeds with the next record.
• A constraint violation or unique-index violation during a DELETE statement causes the database server to make a copy of the record that was to be deleted and write it to the violations table. The database server does not delete the actual record in the target table. If the DELETE statement is not a singleton delete, the rest of the delete operation proceeds with the next record.

In all of these cases, the database server sends diagnostic information about each constraint violation or unique-index violation to the diagnostics table associated with the target table.

For detailed information on the structure of the records that the database server writes to the violations and diagnostics tables, see Structure of the Violations Table and Structure of the Diagnostics Table.

You must use the START VIOLATIONS TABLE statement to start the violations and diagnostics tables for the target table on which the database objects are defined, either before you set any database objects that are defined on the table to the filtering mode, or after you set database objects to filtering, but before any users issue INSERT, DELETE, or UPDATE statements.

If you want to stop the database server from filtering bad records to the violations table and sending diagnostic information about each bad record to the diagnostics table, you must issue a STOP VIOLATIONS TABLE statement.

For further information on these statements, see START VIOLATIONS TABLE and STOP VIOLATIONS TABLE.

When you set the mode of a constraint or unique index to filtering, you can specify one of two error options. These error options control whether the database server displays an integrity-violation error message when it encounters bad records during execution of data manipulation statements.

• The WITHOUT ERROR option is the default error option. The WITHOUT ERROR option signifies that when the database server executes an INSERT, DELETE, or UPDATE statement, and one or more of the target rows causes a constraint violation or unique-index violation, no integrity-violation error message is returned to the user.
• The WITH ERROR option signifies that when the database server executes an INSERT, DELETE, or UPDATE statement, and one or more of the target rows causes a constraint violation or unique-index violation, the database server returns an integrity-violation error message.

The net effect of the filtering mode is that the contents of the target table always satisfy all constraints on the table and any unique-index requirements on the table. In addition, the database server does not lose any data that violates a constraint or unique-index requirement because bad records are sent to the violations table and diagnostic information about those records is sent to the diagnostics table.

Furthermore, when filtering mode is in effect, insert, delete, and update operations on the target table do not fail when the database server encounters bad records. These operations succeed in adding all the good records to the target table. So filtering mode is especially appropriate for large-scale batch updates of tables. The user can fix records that violate constraints and unique-index requirements after the fact. The user does not have to fix the bad records before the batch update or lose the bad records during the batch update.

You can specify the disabled or enabled modes for triggers or duplicate indexes

If you do not specify the mode for a trigger in the CREATE TRIGGER or SET Database Object Mode statement, the trigger is enabled by default.

If you do not specify the mode for an index in the CREATE INDEX or SET Database Object Mode statement, the index is enabled by default.

Related statements: ALTER TABLE, CREATE TABLE, CREATE INDEX, CREATE TRIGGER, START VIOLATIONS TABLE and STOP VIOLATIONS TABLE

For a discussion of object modes and violation detection and examples that show how database object modes work when users execute data manipulation statements on target tables or add new constraints and indexes to target tables, see the Informix Guide to SQL: Tutorial.

For information on the system catalog tables associated with the SET Database Object Mode statement, see the sysobjstate and sysviolations tables in the Informix Guide to SQL: Reference.