Syntax

Usage

• To change the object mode of constraints, indexes, and triggers

When you change the object mode of constraints, indexes, or triggers, the change is permanent. The setting that the SET statement produces remains in effect until you change the object mode of the object again.

• To set the transaction mode of constraints by specifying whether constraints are checked at the statement level or at the transaction level

When you set the transaction mode of constraints, the effect of the SET statement is limited to the transaction in which it is executed. The setting that the SET statement produces is effective only during the transaction. For further information on setting the transaction mode for constraints, see "Transaction-Mode Format".

Terminology for Object Modes

Similarly, the term object modes has a restricted meaning in the context of the SET statement. The term refers to the three states that a database object can have: enabled, disabled, and filtering. The sysobjstate system catalog table lists all of the objects in the database and the current object mode of each object.

Do not confuse the terms objects and object modes as used in the SET statement with the term objects in INFORMIX-NewEra. In the context of INFORMIX-NewEra, objects refers to objects within an application.

Methods for Changing Object Modes

Privileges Required for Changing Object Modes

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

Element	Purpose	Restrictions	Syntax
table name	The name of the table whose objects will have their object mode changed. There is no default value.	The table must be a local table. You cannot set the object modes of objects defined on a temporary table to the disabled or filtering modes. For information on the privileges required to change the object mode of the objects defined on a table, see "Privileges Required for Changing Object Modes".	Identifier, p. 1-966

Use the table-mode format to change the object mode of all objects of a given type that have been defined on a particular table. For example, to change the object mode of all constraints that are defined on the cust_subset table to the disabled mode, enter the following statement:

SET CONSTRAINTS FOR cust_subset DISABLED

SET CONSTRAINTS, INDEXES, TRIGGERS FOR cust_subset

	ENABLED

You can also specify the object mode for a constraint when you create the constraint with the ALTER TABLE or CREATE TABLE statements. If you do not specify the object mode for a constraint in one of these statements or in a SET statement, the constraint is in the enabled object mode by default.

You can also specify the object mode for a unique index when you create the index with the CREATE INDEX statement. If you do not specify the object mode for a unique index in the CREATE INDEX statement or in a SET statement, the unique index is in the enabled object mode by default.

For definitions of the disabled, enabled, and filtering object modes see "Using Object Modes with Data Manipulation Statements". For an explanation of the benefits of these object modes, see "Benefits of Object Modes".

Error Options for Filtering Mode

WITHOUT ERROR Option

WITH ERROR Option

Scope of Error Options

These error options do not apply when you attempt to change the object mode of a disabled constraint or disabled unique index to the enabled or filtering mode, and the SET statement fails because one or more rows in the target table violates the constraint or the unique-index requirement. In these cases, if a violations table has been started for the table that contains the inconsistent data, the database server returns an integrity-violation error message regardless of the error option that is specified in the SET statement.

Violations and Diagnostics Tables for Filtering Mode

When to Start the Violations and Diagnostics Tables

If you have not started a violations and diagnostics table for the target table with filtering-mode objects, the database server does not return an error until an INSERT, DELETE, or UPDATE statement fails to satisfy an integrity requirement on the table. If an INSERT, DELETE, or UPDATE statement fails to satisfy the constraint or unique-index requirement for a particular row, the database server cannot filter the bad row to the violations table because no violations table is associated with the target table. The user receives an error message indicating that no violations table has been started for the target table.

To prevent such errors, start the violations and diagnostics tables for the target table at one of the following points:

• You can start the violations and diagnostics tables before you set any objects that are defined on the table to the filtering mode.
• You can start the violations and diagnostics tables after you set objects to the filtering mode but before any users issue INSERT, DELETE, or UPDATE statements that could violate any integrity requirements on the target table.

For further information on the START VIOLATIONS TABLE statement and the structure of the violations and diagnostics tables themselves, see the START VIOLATIONS TABLE statement on page 1-748.

How to Stop the Violations and Diagnostics Tables

For further information on using the STOP VIOLATIONS TABLE statement, see the STOP VIOLATIONS TABLE statement on page 1-767.

Object Modes for Triggers and Duplicate Indexes

You can also specify the object mode for a trigger when you create the trigger with the CREATE TRIGGER statement. If you do not specify the object mode for a trigger in the CREATE TRIGGER statement or in a SET statement, the trigger is in the enabled object mode by default.

You can also specify the object mode for a duplicate index when you create the index with the CREATE INDEX statement. If you do not specify the object mode for a duplicate index in the CREATE INDEX statement or in a SET statement, the duplicate index is in the enabled object mode by default.

For definitions of the disabled and enabled object modes, see "Using Object Modes with Data Manipulation Statements". For an explanation of the benefits of these two object modes, see "Benefits of Object Modes".

List-Mode Format

Element	Purpose	Restrictions	Syntax
constraint name	The name of the constraint whose object mode is to be set, or a list of constraint names. There is no default value.	Each constraint in the list must be a local constraint. All constraints in the list must be defined on the same table.	Identifier, p. 1-966
index name	The name of the index whose object mode is to be set, or a list of index names. There is no default value.	Each index in the list must be a local index. All indexes in the list must be defined on the same table.	Identifier, p. 1-966
trigger name	The name of the trigger whose object mode is to be set, or a list of trigger names. There is no default value.	Each trigger in the list must be a local trigger. All triggers in the list must be defined on the same table.	Identifier, p. 1-966

SET INDEXES unq_ssn FILTERING

SET TRIGGERS insert_trig, update_trig, delete_trig DISABLED

What do we mean by the terms enabled, disabled, and filtering? Definitions of these object modes follow. These definitions explain how each object mode affects tables and data manipulation statements. The definitions focus on the object modes of constraints as an illustration, but the same principles apply to indexes and triggers as well.

Definition of Enabled Mode

Definition of Disabled Mode

Definition of Filtering Mode

However, the database server handles data manipulation statements differently for objects in enabled and filtering mode, as the following paragraphs describe:

• If a constraint or unique index is in the enabled mode, the database server carries out the INSERT, UPDATE, or DELETE statement only if all the target rows affected by the statement satisfy the constraint or the unique index requirement. The database server updates all the target rows in the table.
• If a constraint or unique index is in the filtering mode, the database server carries out the INSERT, UPDATE, or DELETE statement even if one or more of the target rows fail to satisfy the constraint or the unique index requirement. The database server updates the good rows in the table (the target rows that satisfy the constraint or unique index requirement). The database server does not update the bad rows in the table (that is, the target rows that fail to satisfy the constraint or unique index requirement). Instead the database server sends each bad row to a special table called the violations table. The database server places information about the nature of the violation for each bad row in another special table called the diagnostics table.

Assume that user joe has defined the lname column as not null but has not assigned a name to the not null constraint, so the database server has implicitly assigned the name n104_7 to this constraint. Finally, assume that user joe has created a unique index named unq_ssn on the ssn column.

Now a user linda who has the Insert privilege on the cust_subset table enters the following INSERT statement on this table:

INSERT INTO cust_subset (ssn, fname, city)

	VALUES (973824499, "jane", "los altos")

• If the constraint is disabled, the row is inserted in the target table, and no error is returned to the user.
• If the constraint is enabled, the row is not inserted in the target table. A constraint-violation error is returned to the user, and the effects of the statement are rolled back (if the database is a Universal Server database with logging).
• If the constraint is filtering, the row is not inserted in the target table. Instead the row is inserted in the violations table. Information about the integrity violation caused by the row is placed in the diagnostics table. The effects of the INSERT statement are not rolled back. You receive an error message if you specified the WITH ERROR option for the filtering-mode constraint. By analyzing the contents of the violations and the diagnostics tables, you can identify the reason for the failure and either take corrective action or roll back the operation.

Results of the Insert Operation When the Constraint Is Disabled

Results of the Insert Operation When the Constraint Is Enabled

-292 An implied insert column lname does not accept NULLs.

Assume that user joe has started a violations and diagnostics table for the cust_subset table. The violations table is named cust_subset_vio, and the diagnostics table is named cust_subset_dia. The new row added to the cust_subset_vio violations table when user linda issues the INSERT statement on the cust_subset target table has the following column values.

ssn	fname	lname	city	informix_tupleid	informix_optype	informix_recowner
973824499	jane	NULL	los altos	1	I	linda

This new row in the cust_subset_vio violations table has the following characteristics:

• The first four columns of the violations table exactly match the columns of the target table. These four columns have the same names and the same data types as the corresponding columns of the target table, and they have the column values that were supplied by the INSERT statement that user linda entered.
• The value 1 in the informix_tupleid column is a unique serial identifier that is assigned to the nonconforming row.
• The value I in the informix_optype column is a code that identifies the type of operation that has caused this nonconforming row to be created. Specifically, I stands for an insert operation.
• The value linda in the informix_recowner column identifies the user who issued the statement that caused this nonconforming row to be created.

This new diagnostic row in the cust_subset_dia diagnostics table has the following characteristics:

• This row of the diagnostics table is linked to the corresponding row of the violations table by means of the informix_tupleid column that appears in both tables. The value 1 appears in this column in both tables.
• The value C in the objtype column identifies the type of integrity violation that the corresponding row in the violations table caused. Specifically, the value C stands for a constraint violation.
• The value joe in the objowner column identifies the owner of the constraint for which an integrity violation was detected.
• The value n104_7 in the objname column gives the name of the constraint for which an integrity violation was detected.

Multiple Diagnostic Rows for One Violations Row

Both rows in the diagnostics table correspond to the same row of the violations table because both of these rows have the value 1 in the informix_tupleid column. However, the first diagnostic row identifies the constraint violation caused by the INSERT statement that user linda issued, while the second diagnostic row identifies the unique-index violation caused by the same INSERT statement. In this second diagnostic row, the value I in the objtype column stands for a unique-index violation, and the value unq_ssn in the objname column gives the name of the index for which the integrity violation was detected.

For information on when and how to start violations and diagnostics tables for a target table, see "Violations and Diagnostics Tables for Filtering Mode". For further information on the structure of the violations and diagnostics tables, see the START VIOLATIONS TABLE statement on page 1-748.

Using Object Modes to Achieve Data Integrity

You can add a new constraint or index easily by taking the following steps. If you follow this procedure, you do not have to examine the entire target table to identify rows that fail to satisfy the constraint or unique-index requirement:

• Add the constraint or index in the enabled mode. If all existing rows in the table satisfy the constraint or unique-index requirement, your ALTER TABLE or CREATE INDEX statement executes successfully, and you do not need to take any further steps. However, if any existing rows in the table fail to satisfy the constraint or unique-index requirement, your ALTER TABLE or CREATE INDEX statement returns an error message, and you need to take the following steps.
• Add the constraint or index in the disabled mode. Issue the ALTER TABLE statement again, and specify the DISABLED keyword in the ADD CONSTRAINT or MODIFY clause; or issue the CREATE INDEX statement again, and specify the DISABLED keyword.
• Start a violations and diagnostics table for the target table with the START VIOLATIONS TABLE statement.
• Issue a SET statement to switch the object mode of the constraint or index to the enabled mode. When you issue this statement, the statement fails, and existing rows in the target table that violate the constraint or the unique-index requirement are duplicated in the violations table. The constraint or index remains disabled, and you receive an integrity-violation error message.
• Issue a SELECT statement on the violations table to retrieve the nonconforming rows that were duplicated from the target table. You might need to join the violations and diagnostics tables to get all the necessary information.
• Take corrective action on the rows in the target table that violate the constraint.
• After you fix all the nonconforming rows in the target table, issue the SET statement again to switch the disabled constraint or index to the enabled mode. This time the constraint or index is enabled, and no integrity-violation error message is returned because all rows in the target table now satisfy the new constraint or unique-index requirement.

Also assume that no constraints or unique indexes are defined on the cust_subset table and that the fname column is the primary key. In addition, assume that no violations and diagnostics tables currently exist for this target table. Finally, assume that this table currently contains four rows with the following column values.

ssn	fname	lname	city
111763227	mark	jackson	sunnyvale
222781244	rhonda	NULL	palo alto
111763227	steve	NULL	san mateo
333992276	tammy	jones	san jose

Adding the Objects in the Enabled Mode

CREATE UNIQUE INDEX unq_ssn ON cust_subset (ssn) ENABLED;

ALTER TABLE cust_subset MODIFY (lname CHAR(15)

	NOT NULL CONSTRAINT lname_notblank ENABLED);

Adding the Objects in the Disabled Mode

CREATE UNIQUE INDEX unq_ssn ON cust_subset (ssn) DISABLED;

ALTER TABLE cust_subset MODIFY (lname CHAR(15)

	NOT NULL CONSTRAINT lname_notblank DISABLED);

Starting a Violations and Diagnostics Table

First, user joe enters the following statement to start a violations and diagnostics table for the cust_subset table:

START VIOLATIONS TABLE FOR cust_subset

Using the SET Statement to Capture Violations

SET CONSTRAINTS, INDEXES FOR cust_subset ENABLED

The following table shows the contents of the cust_subset_vio violations table after user joe issues the SET statement.

ssn	fname	lname	city	informix_tupleid	informix_optype	informix_recowner
222781244	rhonda	NULL	palo alto	1	S	joe
111763227	steve	NULL	san mateo	2	S	joe

These two rows in the cust_subset_vio violations table have the following characteristics:

• The row in the cust_subset target table whose fname value is rhonda is duplicated to the cust_subset_vio violations table because this row violates the not null constraint on the lname column.
• The row in the cust_subset target table whose fname value is steve is duplicated to the cust_subset_vio violations table because this row violates the not null constraint on the lname column and the unique-index requirement on the ssn column.
• The value 1 in the informix_tupleid column for the first row and the value 2 in the informix_tupleid column for the second row are unique serial identifiers assigned to the nonconforming rows.
• The value S in the informix_optype column for each row is a code that identifies the type of operation that has caused this nonconforming row to be placed in the violations table. Specifically, the S stands for a SET statement.
• The value joe in the informix_recowner column for each row identifies the user who issued the statement that caused this nonconforming row to be placed in the violations table.

These three rows in the cust_subset_dia diagnostics table have the following characteristics:

• Each row in the diagnostics table and the corresponding row in the violations table are joined by the informix_tupleid column that appears in both tables.
• The first row in the diagnostics table has an informix_tupleid value of 1. It is joined to the row in the violations table whose informix_tupleid value is 1. The value C in the objtype column for this diagnostic row identifies the type of integrity violation that was caused by the corresponding row in the violations table. Specifically, the value C stands for a constraint violation. The value lname_notblank in the objname column for this diagnostic row gives the name of the constraint for which an integrity violation was detected.
• The second row in the diagnostics table has an informix_tupleid value of 2. It is joined to the row in the violations table whose informix_tupleid value is 2. The value C in the objtype column for this second diagnostic row indicates that a constraint violation was caused by the corresponding row in the violations table. The value lname_notblank in the objname column for this diagnostic row gives the name of the constraint for which an integrity violation was detected.
• The third row in the diagnostics table has an informix_tupleid value of 2. It is also joined to the row in the violations table whose informix_tupleid value is 2. The value I in the objtype column for this third diagnostic row indicates that a unique-index violation was caused by the corresponding row in the violations table. The value unq_ssn in the objname column for this diagnostic row gives the name of the index for which an integrity violation was detected.
• The value joe in the objowner column of all three diagnostic rows identifies the owner of the object for which an integrity violation was detected. The name of user joe appears in all three rows because he created the constraint and index on the cust_subset table.

Taking Corrective Action on the Nonconforming Rows

Enabling the Disabled Objects

SET CONSTRAINTS, INDEXES FOR cust_subset ENABLED

Benefits of Object Modes

Benefits of Disabled Mode

• You can use the disabled mode to insert many rows quickly into a target table. Especially during load operations, updates of the existing indexes and enforcement of referential constraints make up a big part of the total cost of the operation. By disabling the indexes and referential constraints during the load operation, you improve the performance and efficiency of the load.
• To add a new constraint or new unique index to an existing table, you can add the object even if some rows in the table do not satisfy the new integrity specification. If the constraint or index is added to the table in disabled mode, your ALTER TABLE or CREATE INDEX statement does not fail no matter how many existing rows violate the new integrity requirement.

If a violations table has been started, a SET statement that switches the disabled objects to the enabled or filtering mode fails, but it causes the nonconforming rows in the target table to be duplicated in the violations table so that you can identify the rows and take corrective action. After you fix the nonconforming rows in the target table, you can reissue the SET statement to switch the disabled objects to the enabled or filtering mode.

Benefits of Enabled Mode

• The benefit of enabled mode for constraints is that the database server enforces the constraint and thus ensures the consistency of the data in the database.
• The benefit of enabled mode for indexes is that the database server updates the index after insert, delete, and update operations. Thus the index is up to date and is used by the optimizer during database queries.
• The benefit of enabled mode for triggers is that the trigger event always sets the triggered action in motion. Thus the purpose of the trigger is always realized during actual data-manipulation operations.

• During load operations, inserts that violate a filtering mode constraint or unique index do not cause the load operation to fail. Instead, the database server filters the bad rows to the violations table and continues the load operation.
• When an INSERT, DELETE, or UPDATE statement that affects multiple rows causes a filtering mode constraint or unique index to be violated for a particular row or rows, the statement does not fail. Instead, the database server filters the bad row or rows to the violations table and continues to execute the statement.
• When any INSERT, DELETE, or UPDATE statement violates a filtering mode constraint or unique index, the user can identify the failed row or rows and take corrective action. The violations and diagnostics tables capture the necessary information, and users can take corrective action after they analyze this information.i

You use the IMMEDIATE keyword to set the transaction mode of constraints to statement-level checking. You use the DEFERRED keyword to set the transaction mode to transaction-level checking.

You can set the transaction mode of constraints only in a database with logging.

Statement-Level Checking

Transaction-Level Checking

Tip: If you defer checking a primary-key constraint, the checking of the not-null constraint for that column or set of columns is also deferred.

Duration of Transaction Modes

Switching Transaction Modes

Specifying All Constraints or a List of Constraints

Specifying All Constraints

Specifying a List of Constraints

When you specify a list of constraints, the constraints do not have to be defined on the same table, but they must exist in the same database.

Specifying Remote Constraints

Examples of Setting the Transaction Mode for Constraints

BEGIN WORK

SET CONSTRAINTS ALL DEFERRED

.

.

.

COMMIT WORK

BEGIN WORK

SET CONSTRAINTS update_const, insert_const DEFERRED

.

.

.

COMMIT WORK

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