INFORMIX
Informix Guide to SQL: Syntax
Chapter 1: SQL Statements
Home Contents Index Master Index New Book

SQL Statements

SET

 The SET statement allows you to change the object mode of the following database objects: constraints, indexes, and triggers. You can also use the SET statement to specify the transaction mode of constraints.

Syntax

Usage

The SET statement has the following purposes:

    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.

Terminology for Object Modes

The SET statement operates on database objects by changing the object mode of those objects. The terms database objects and objects have a restricted meaning in the context of the SET statement. Both terms refer to the constraints, indexes, and triggers in a database.

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

The SET statement provides the following formats for changing object modes: table mode and list mode. For an explanation of the table-mode format, see "Table-Mode Format". For an explanation of the list-mode format, see "List-Mode Format".

Privileges Required for Changing Object Modes

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

Table-Mode Format

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:

By using the table-mode format, you can change the object modes of more than one object type with a single SET statement. For example, to change the object mode of all constraints, indexes, and triggers that are defined on the cust_subset table to the enabled mode, enter the following statement:

Object Modes for Constraints and Unique Indexes

You can specify the disabled, enabled, or filtering object modes for a constraint or a unique index. You must specify one of these object modes in your SET statement. The SET statement has no default object mode.

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

When you change the object mode of a constraint or unique index to the filtering mode, you can specify the following error options: WITHOUT ERROR or WITH ERROR.

WITHOUT 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 WITHOUT ERROR option is the default error option.

WITH ERROR Option

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, an integrity-violation error message is returned to the user.

Scope of Error Options

The WITH ERROR and WITHOUT ERROR options apply only 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. These error options control whether the database server displays an integrity-violation error message after it executes these statements.

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 you specify the filtering mode for constraints or unique indexes in a SET statement, violations and diagnostics tables are not automatically started for the target table. When you set objects to the filtering mode, be sure to start the violations and diagnostics tables for the target table on which the filtering mode objects are defined. The violations table captures rows that fail to meet integrity requirements. The diagnostics table captures information about each row that fails to meet integrity requirements.

When to Start the Violations and Diagnostics Tables

You are not required to start the violations and diagnostics tables before you set objects to the filtering mode. If you have not started a violations and diagnostics table when you set an object to the filtering mode, the database server executes your SET statement and does not return an error. Similarly, if you issue an INSERT, DELETE, or UPDATE statement on the target table, and you have not started a violations and diagnostics table for the target table, the database server executes the statement and does not return an error as long as all of the integrity requirements on the table are satisfied.

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:

How to Start the Violations and Diagnostics Tables

 To create the violations and diagnostics tables and associate them with the target table, use the START VIOLATIONS TABLE statement. In this statement, specify the name of the target table for which the violations and diagnostics tables are to be started. You can also assign names to the violations and diagnostics tables in this statement.

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

After you turn off filtering mode for the objects that are defined on a target table, and you no longer need the violations and diagnostics tables, use the STOP VIOLATIONS TABLE statement to drop the association between the target table and the violations and diagnostics tables. In this statement, you specify the name of the target table whose association with the violations and diagnostics tables is to be dropped.

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 specify the disabled or enabled object modes for triggers or duplicate indexes. You must specify one of these object modes in your SET statement. The SET statement has no default object mode.

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

Use the list-mode format to change the object mode for a particular constraint, index, or trigger. For example, to change the object mode of the unique index unq_ssn on the cust_subset table to filtering mode, enter the following statement:

You can also use the list-mode format to change the object 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 in the enabled mode. To change the object mode of all the triggers except execute_trig to the disabled mode, enter the following statement:

Using Object Modes with Data Manipulation Statements

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

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

Constraints, indexes, and triggers are in the enabled mode by default. When an object is in the enabled mode, the database server recognizes the existence of the object and takes the object into consideration while it executes data manipulation statements. For example, when a constraint is enabled, any INSERT, UPDATE, or DELETE statement that violates the constraint fails, and the target row remains unchanged. In addition, the user receives an error message.

Definition of Disabled Mode

When an object is in the disabled mode, the database server acts as if the object did not exist and does not take it into consideration during the execution of data manipulation statements. For example, when a constraint is disabled, any INSERT, UPDATE, or DELETE statement that violates the constraint succeeds, and the target row is changed. The user does not receive an error message.

Definition of Filtering Mode

When an object is in the filtering mode, the object behaves the same as in the enabled mode in that the database server recognizes the existence of the object during INSERT, UPDATE, and DELETE statements. For example, when a constraint is in the filtering mode, and an INSERT, DELETE, or UPDATE statement is executed, any target rows that violate the constraint remain unchanged.

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

Example of Object Modes with Data Manipulation Statements

 An example with the INSERT statement can illustrate the differences between the enabled, disabled, and filtering modes. Consider an INSERT statement in which a user tries to add a row that does not satisfy an integrity constraint on a table. For example, assume that a user joe has created a table named cust_subset, and this table consists of the following columns: ssn (customer's social security number), fname (customer's first name), lname (customer's last name), and city (city in which the customer lives). The ssn column has the INT data type. The other three columns have the CHAR data type.

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:

User linda has entered values for all the columns of the new row except for the lname column, even though the lname column has been defined as a not null column. The database server behaves in the following ways, depending on the object mode of the constraint:

We can better grasp the distinctions among disabled, enabled, and filtering modes by viewing the actual results of the INSERT statement shown in the preceding example.

Results of the Insert Operation When the Constraint Is Disabled

If the not-null constraint on the cust_subset table is disabled, the INSERT statement that user linda issues successfully inserts the new row in this table. The new row of the cust_subset table has the following column values.

ssn fname lname city

973824499

jane

NULL

los altos

Results of the Insert Operation When the Constraint Is Enabled

If the not-null constraint on the cust_subset table is enabled, the INSERT statement fails to insert the new row in this table. Instead user linda receives the following error message when she enters the INSERT statement:

Results of the Insert When Constraint Is in Filtering Mode

 If the not-null constraint on the cust_subset table is set to the filtering mode, the INSERT statement that user linda issues fails to insert the new row in this table. Instead the new row is inserted into the violations table, and a diagnostic row that describes the integrity violation is added to the diagnostics table.

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 INSERT statement that user linda issued on the cust_subset target table also causes a diagnostic row to be added to the cust_subset_dia diagnostics table. The new diagnostic row added to the diagnostics table has the following column values.

informix_tupleid objtype objowner objname

1

C

joe

n104_7

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

By joining the violations and diagnostics tables, user joe (who owns the cust_subset target table and its associated special tables) or the DBA can find out that the row in the violations table whose informix_tupleid value is 1 was created after an INSERT statement and that this row is violating a constraint. The table owner or DBA can query the sysconstraints system catalog table to determine that this constraint is a not null constraint. Now that the reason for the failure of the INSERT statement is known, user joe or the DBA can take corrective action.

Multiple Diagnostic Rows for One Violations Row

In the preceding example, only one row in the diagnostics table corresponds to the new row in the violations table. However, more than one diagnostic row can be added to the diagnostics table when a single new row is added to the violations table. For example, if the ssn value (973824499) that user linda entered in the INSERT statement had been the same as an existing value in the ssn column of the cust_subset target table, only one new row would appear in the violations table, but the following two diagnostic rows would be present in the cust_subset_dia diagnostics table.

informix_tupleid objtype objowner objname

1

C

joe

n104_7

1

I

joe

unq_ssn

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

In addition to using object modes with data manipulation statements, you can also use object modes when you add a new constraint or new unique index to a target table. By selecting the correct object mode, you can add the constraint or index to the target table easily even if existing rows in the target table violate the new integrity specification.

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:

Example of Using Object Modes to Achieve Data Integrity

 The following example shows how to use object modes to add a constraint and unique index to a target table easily. Assume that a user joe has created a table named cust_subset, and this table consists of the following columns: ssn (customer's social security number), fname (customer's first name), lname (customer's last name), and city (city in which the customer lives).

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

User joe, the owner of the cust_subset table, enters the following statements to add a unique index on the ssn column and a not null constraint on the lname column:

Both of these statements fail because existing rows in the cust_subset table violate the integrity specifications. The row whose fname value is rhonda violates the not null constraint on the lname column. The row whose fname value is steve violates both the not null constraint on the lname column and the unique-index requirement on the ssn column.

Adding the Objects in the Disabled Mode

To recover from the preceding errors, user joe reenters the CREATE INDEX and ALTER TABLE statements and specifies the disabled mode in both statements, as follows:

Both of these statements execute successfully because the database server does not enforce unique-index requirements or constraint specifications when these objects are disabled.

Starting a Violations and Diagnostics Table

Now that the new constraint and index are added for the cust_subset table, user joe takes steps to find out which existing rows in the cust_subset table violate the constraint and the index.

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

Because user joe has not assigned names to the violations and diagnostics tables in this statement, the tables are named cust_subset_vio and cust_subset_dia by default.

Using the SET Statement to Capture Violations

Now that violations and diagnostics tables exist for the target table, user joe issues the following SET statement to switch the mode of the new index and constraint from the disabled mode to the enabled mode:

The result of this SET statement is that the existing rows in the cust_subset table that violate the constraint and the unique-index requirement are copied to the cust_subset_vio violations table, and diagnostic information about the nonconforming rows is added to the cust_subset_dia diagnostics table. The SET statement fails, and the constraint and index remain disabled.

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 following table shows contents of the cust_subset_dia diagnostics table after user joe issues the SET statement

informix_tupleid objtype objowner objname

1

C

joe

lname_notblank

2

C

joe

lname_notblank

2

I

joe

unq_ssn

.

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

Identifying Nonconforming Rows to Obtain Information

 To determine the contents of the violations table, user joe enters a SELECT statement to retrieve all rows from the table. Then, to obtain complete diagnostic information about the nonconforming rows, user joe joins the violations and diagnostics tables by means of another SELECT statement. User joe can perform these operations either interactively or through a program.

Taking Corrective Action on the Nonconforming Rows

After the user joe identifies the nonconforming rows in the cust_subset table, he can correct them. For example, he can enter UPDATE statements on the cust_subset table either interactively or through a program.

Enabling the Disabled Objects

Once all the nonconforming rows in the cust_subset table are corrected, user joe issues the following SET statement to set the new constraint and index to the enabled mode:

This time the SET statement executes successfully. The new constraint and new unique index are enabled, and no error message is returned to user joe because all rows in the cust_subset table now satisfy the new constraint specification and unique-index requirement.

Benefits of Object Modes

The preceding examples show how object modes work when users execute data manipulation statements on target tables or add new constraints and indexes to target tables. The preceding examples suggest some of the benefits of the different object modes. The following sections state these benefits explicitly.

Benefits of Disabled Mode

The benefits of the disabled mode are as follows:

    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 enabled mode is the default object mode for all database objects. We can summarize the benefits of this mode for each type of database object as follows:

Benefits of Filtering Mode

 The benefits of setting a constraint or unique index to the filtering mode are as follows:

Transaction-Mode Format

Element Purpose Restrictions Syntax

constraint name

The name of the constraint whose transaction mode is to be changed, or a list of constraint names. There is no default value.

The specified constraint must exist in a database with logging. You cannot change the transaction mode of a constraint to deferred mode unless the constraint is currently in the enabled mode. All constraints in a list of constraints must exist in the same database.

Identifier, p. 1-966

You can use the transaction-mode format of the SET statement to set the transaction mode of constraints.

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

When you set the transaction mode to immediate, statement-level checking is turned on, and all specified constraints are checked at the end of each INSERT, UPDATE, or DELETE statement. If a constraint violation occurs, the statement is not executed. Immediate is the default transaction mode of constraints.

Transaction-Level Checking

When you set the transaction mode of constraints to deferred, statement-level checking is turned off, and all specified constraints are not checked until the transaction is committed. If a constraint violation occurs while the transaction is being committed, the transaction is rolled back.

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

The duration of the transaction mode that the SET statement specifies is the transaction in which the SET statement is executed. You cannot execute this form of the SET statement outside a transaction. Once a COMMIT WORK or ROLLBACK WORK statement is successfully completed, the transaction mode of all constraints reverts to IMMEDIATE.

Switching Transaction Modes

To switch from transaction-level checking to statement-level checking, you can use the SET statement to set the transaction mode to immediate, or you can use a COMMIT WORK or ROLLBACK WORK statement in your transaction.

Specifying All Constraints or a List of Constraints

You can specify all constraints in the database in your SET statement, or you can specify a single constraint or list of constraints.

Specifying All Constraints

If you specify the ALL keyword, the SET statement sets the transaction mode for all constraints in the database. If any statement in the transaction requires that any constraint on any table in the database be checked, the database server performs the checks at the statement level or the transaction level, depending on the setting that you specify in the SET statement.

Specifying a List of Constraints

If you specify a single constraint name or a list of constraints, the SET statement sets the transaction mode for the specified constraints only. If any statement in the transaction requires checking of a constraint that you did not specify in the SET statement, that constraint is checked at the statement level regardless of the setting that you specified in the SET statement for other 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

You can set the transaction mode of local constraints or remote constraints. That is, the constraints that are specified in the transaction-mode form of the SET statement can be constraints that are defined on local tables or constraints that are defined on remote tables.

Examples of Setting the Transaction Mode for Constraints

The following example shows how to defer checking constraints within a transaction until the transaction is complete. The SET CONSTRAINTS statement in the example specifies that any constraints on any tables in the database are not checked until the COMMIT WORK statement is encountered.

The following example specifies that a list of constraints is not checked until the transaction is complete:

References

 See the START VIOLATIONS TABLE and STOP VIOLATIONS TABLE statements in this manual.

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



Informix Guide to SQL: Syntax, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.