Informix Guide to SQL: Syntax SQL Statements

Use the START VIOLATIONS TABLE statement to create a violations table and a diagnostics table for a specified target table. The database server associates the violations and diagnostics tables with the target table by recording the relationship among the three tables in the sysviolations system catalog table.

In Enterprise Decision Server, the START VIOLATIONS TABLE statement creates a violations table but not a diagnostics table.

The START VIOLATIONS TABLE statement creates the special violations table that holds rows that fail to satisfy constraints and unique indexes during insert, update, and delete operations on target tables. This statement also creates the special diagnostics table that contains information about the integrity violations caused by each row in the violations table.

In Enterprise Decision Server, the START VIOLATIONS TABLE statement creates a violations table but not a diagnostics table.

The START VIOLATIONS TABLE statement is closely related to the SET Database Object Mode statement. If you use the SET Database Object Mode statement to set the constraints or unique indexes defined on a table to the filtering database object mode, but you do not use the START VIOLATIONS TABLE statement to start the violations and diagnostics tables for this target table, any rows that violate a constraint or unique-index requirement during an insert, update, or delete operation are not filtered out to a violations table. Instead you receive an error message indicating that you must start a violations table for the target table.

Similarly, if you use the SET Database Object Mode statement to set a disabled constraint or disabled unique index to the enabled or filtering database object mode, but you do not use the START VIOLATIONS TABLE statement to start the violations and diagnostics tables for the table on which the database objects are defined, any existing rows in the table that do not satisfy the constraint or unique-index requirement are not filtered out to a violations table. If, in these cases, you want the ability to identify existing rows that do not satisfy the constraint or unique-index requirement, you must issue the START VIOLATIONS TABLE statement to start the violations and diagnostics tables before you issue the SET Database Object Mode statement to set the database objects to the enabled or filtering database object mode.

In Enterprise Decision Server, the SET Database Object Mode statement is not supported, and the concept of database object modes does not exist. Instead, once you use the START VIOLATIONS TABLE statement to create a violations table and associate it with a target table, the existence of this violations table causes all violations of constraints and unique-index requirements by insert, delete, and update operations to be recorded in the violations table.

In other words, once you issue a START VIOLATIONS TABLE statement, all constraints and unique indexes in a database on Enterprise Decision Server behave like filtering-mode constraints and filtering-mode unique indexes in a database on Dynamic Server. For an explanation of the behavior of filtering-mode constraints and filtering-mode unique indexes, see Filtering Mode.

A transaction must issue the START VIOLATIONS TABLE statement in isolation. That is, no other transaction can be in progress on a target table when a transaction issues a START VIOLATIONS TABLE statement on that table. However, any transactions that start on the target table after the first transaction has issued the START VIOLATIONS TABLE statement will behave the same way as the first transaction with respect to the violations and diagnostics tables. That is, any constraint and unique-index violations by these subsequent transactions will be recorded in the violations and diagnostics tables.

For example, if transaction A operates on table tab1 and issues a START VIOLATIONS TABLE statement on table tab1, the database server starts a violations table named tab1_vio and filters any constraint or unique-index violations on table tab1 by transaction A to table tab1_vio. If transactions B and C start on table tab1 after transaction A has issued the START VIOLATiONS TABLE statement, the database server also filters any constraint and unique-index violations by transactions B and C to table tab1_vio.

However, the result is that all three transactions do not receive error messages about constraint and unique-index violations even though transactions B and C do not expect this behavior. For example, if transaction B issues an INSERT or UPDATE statement that violates a check constraint on table tab1, the database server does not issue a constraint violation error to transaction B. Instead, the database server filters the bad row to the violations table without notifying transaction B that a data-integrity violation occurred.

You can prevent this situation from arising in Dynamic Server by specifying the WITH ERRORS option when you set database objects to the FILTERING mode in a SET Database Object Mode, CREATE TABLE, ALTER TABLE, or CREATE INDEX statement. When multiple transactions operate on a table and the WITH ERRORS option is in effect, any transaction that violates a constraint or unique-index requirement on a target table receives a data-integrity error message.

In Enterprise Decision Server, once a transaction issues a START VIOLATIONS TABLE statement, you have no way to make the database server issue data-integrity violation messages to that transaction or to any other transactions that start subsequently on the same target table.

After you use a START VIOLATIONS TABLE statement to create an association between a target table and the violations and diagnostics tables, the only way to drop the association between the target table and the violations and diagnostics tables is to issue a STOP VIOLATIONS TABLE statement for the target table. For more information, see STOP VIOLATIONS TABLE.

You can use the USING clause to assign explicit names to the violations and diagnostics tables.

If you omit the USING clause, the database server assigns names to the violations and diagnostics tables. The system-assigned name of the violations table consists of the name of the target table followed by the string vio. The system-assigned name of the diagnostics table consists of the name of the target table followed by the string dia.

If you omit the USING clause, the maximum length of the target table is 124 characters.

You can use the USING clause to assign an explicit name to the violations table.

If you omit the USING clause, the database server assigns a name to the violations table. The system-assigned name of the violations table consists of the name of the target table followed by the string vio.

If you omit the USING clause, the maximum length of the target table is 14 characters.

You can use the MAX ROWS clause to specify the maximum number of rows that the database server can insert into the diagnostics table when a single statement is executed on the target table.

If you do not include the MAX ROWS clause in the START VIOLATIONS TABLE statement, no upper limit exists on the number of rows that can be inserted into the diagnostics table when a single statement is executed on the target table.

You can use the MAX VIOLATIONS clause to specify the maximum number of rows that any single coserver can insert into the violations table when a single statement is executed on the target table. Each coserver where the violations table resides has this limit. The first coserver to reach this limit raises an error and causes the statement to fail.

If you do not include the MAX VIOLATIONS clause in a START VIOLATIONS TABLE statement, no upper limit exists on the number of rows that can be inserted into the violations table when a single statement is executed on the target table.

The following examples show different ways to execute the START VIOLATIONS TABLE statement.

The following statement starts violations and diagnostics tables for the target table named cust_subset. The violations table is named cust_subset_vio by default, and the diagnostics table is named cust_subset_dia by default.

START VIOLATIONS TABLE FOR cust_subset

The following statement starts a violations and diagnostics table for the target table named items. The USING clause assigns explicit names to the violations and diagnostics tables. The violations table is to be named exceptions, and the diagnostics table is to be named reasons.

START VIOLATIONS TABLE FOR items

USING exceptions, reasons

The following statement starts violations and diagnostics tables for the target table named orders. The MAX ROWS clause specifies the maximum number of rows that can be inserted into the diagnostics table when a single statement, such as an INSERT statement, is executed on the target table.

START VIOLATIONS TABLE FOR orders MAX ROWS 50000

The following statement starts a violations table for the target table named orders. The MAX VIOLATIONS clause specifies the maximum number of rows that any single coserver can insert into the violations table when a single statement, such as an INSERT statement, is executed on the target table.

START VIOLATIONS TABLE FOR orders MAX VIOLATIONS 50000

To start a violations and diagnostics table for a target table, you must meet one of the following requirements:

• You must have the DBA privilege on the database.
• You must be the owner of the target table and have the Resource privilege on the database.
• You must have the Alter privilege on the target table and the Resource privilege on the database.

When you issue a START VIOLATIONS TABLE statement for a target table, the violations table that the statement creates has a predefined structure. This structure consists of the columns of the target table and three additional columns.

The following table shows the structure of the violations table.

Users can take advantage of the relationships among the target table, violations table, and diagnostics table to obtain complete diagnostic information about rows that have caused data-integrity violations during INSERT, DELETE, and UPDATE statements.

Each row of the violations table has at least one corresponding row in the diagnostics table. The row in the violations table contains a copy of the row in the target table for which a data-integrity violation was detected. The row in the diagnostics table contains information about the nature of the data-integrity violation caused by the bad row in the violations table. The row in the violations table has a unique serial identifier in the informix_tupleid column. The row in the diagnostics table has the same serial identifier in its informix_tupleid column.

A given row in the violations table can have more than one corresponding row in the diagnostics table. The multiple rows in the diagnostics table all have the same serial identifier in their informix_tupleid column so that they are all linked to the same row in the violations table. Multiple rows can exist in the diagnostics table for the same row in the violations table because a bad row in the violations table can cause more than one data-integrity violation.

For example, a bad row can violate a unique-index requirement for one column, a not-null constraint for another column, and a check constraint for yet another column. In this case, the diagnostics table contains three rows for the single bad row in the violations table. Each of these diagnostic rows identifies a different data-integrity violation that the nonconforming row in the violations table caused.

By joining the violations and diagnostics tables, the DBA or target-table owner can obtain complete diagnostic information about any or all bad rows in the violations table. You can use SELECT statements to perform these joins interactively, or you can write a program to perform them within transactions.

When you issue the START VIOLATIONS TABLE statement to create the violations table, the database server uses the set of privileges granted on the target table as a basis for granting privileges on the violations table. However, the database server follows different rules when it grants each type of privilege.

The following table explains the circumstances under which the database server grants each privilege on the violations table. The first column lists each privilege. The second column explains the conditions under which the database server grants that privilege to a user.

The following rules apply to ownership of the violations table and privileges on the violations table:

• When the violations table is created, the owner of the target table becomes the owner of the violations table.
• The owner of the violations table automatically receives all table-level privileges on the violations table, including the Alter and References privileges. However, the database server prevents the owner of the violations table from altering the violations table or adding a referential constraint to the violations table.
• You can use the GRANT and REVOKE statements to modify the initial set of privileges on the violations table.
• When you issue an INSERT, DELETE, or UPDATE statement on a target table that has a filtering-mode unique index or constraint defined on it, you must have the Insert privilege on the violations and diagnostics tables.

If you do not have the Insert privilege on the violations and diagnostics tables, the database server executes the INSERT, DELETE, or UPDATE statement on the target table provided that you have the necessary privileges on the target table. The database server does not return an error concerning the lack of insert permission on the violations and diagnostics tables unless an integrity violation is detected during the execution of the INSERT, DELETE, or UPDATE statement.

Similarly, when you issue a SET Database Object Mode statement to set a disabled constraint or disabled unique index to the enabled or filtering mode, and a violations table and diagnostics table exist for the target table, you must have the Insert privilege on the violations and diagnostics tables.

If you do not have the Insert privilege on the violations and diagnostics tables, the database server executes the SET Database Object Mode statement provided that you have the necessary privileges on the target table. The database server does not return an error concerning the lack of insert permission on the violations and diagnostics tables unless an integrity violation is detected during the execution of the SET Database Object Mode statement.

• The grantor of the initial set of privileges on the violations table is the same as the grantor of the privileges on the target table. For example, if the user henry was granted the Insert privilege on the target table by both the user jill and the user albert, the Insert privilege on the violations table is granted to user henry both by user jill and by user albert.
• Once a violations table is started for a target table, revoking a privilege on the target table from a user does not automatically revoke the same privilege on the violations table from that user. Instead you must explicitly revoke the privilege on the violations table from the user.
• If you have fragment-level privileges on the target table, you have the corresponding fragment-level privileges on the violations table.

The following example illustrates how the initial set of privileges on a violations table is derived from the current set of privileges on the target table.

For example, assume that you have a table named cust_subset that holds customer data. This table consists of the following columns: ssn (customer social security number), fname (customer first name), lname (customer last name), and city (city in which the customer lives).

The following set of privileges exists on the cust_subset table:

• User barbara has the Insert and Index privileges on the table. She also has the Select privilege on the ssn and lname columns.
• User carrie has the Update privilege on the city column. She also has the Select privilege on the ssn column.
• User danny has the Alter privilege on the table.

Now user alvin starts a violations table named cust_subset_viols and a diagnostics table named cust_subset_diags for the cust_subset table, as follows:

START VIOLATIONS TABLE FOR cust_subset

	USING cust_subset_viols, cust_subset_diags

The database server grants the following set of initial privileges on the cust_subset_viols violations table:

• User alvin is the owner of the violations table, so he has all table-level privileges on the table.
• User barbara has the Insert, Delete, and Index privileges on the violations table.

User barbara has the Select privilege on the following columns of the violations table: the ssn column, the lname column, the informix_tupleid column, the informix_optype column, and the informix_recowner column.

• User carrie has the Insert and Delete privileges on the violations table.

User carrie has the Update privilege on the following columns of the violations table: the city column, the informix_tupleid column, the informix_optype column, and the informix_recowner column. However, user carrie cannot update the informix_tupleid column because this column is a SERIAL column.

User carrie has the Select privilege on the following columns of the violations table: the ssn column, the informix_tupleid column, the informix_optype column, and the informix_recowner column.

• User danny has no privileges on the violations table.

The following rules concern the structure and use of the violations table:

• Every pair of update rows in the violations table has the same value in the informix_tupleid column to indicate that both rows refer to the same row in the target table.
• If the target table has columns named informix_tupleid, informix_optype, or informix_recowner, the database server attempts to generate alternative names for these columns in the violations table by appending a digit to the end of the column name (for example, informix_tupleid1). If this attempt fails, the database server returns an error, and the violations table is not started for the target table.
• When a table functions as a violations table, it cannot have triggers or constraints defined on it.
• When a table functions as a violations table, users can create indexes on the table, even though the existence of an index affects performance. Unique indexes on the violations table cannot be set to the filtering database object mode.
• If a target table has a violations and diagnostics table associated with it, dropping the target table in cascade mode (the default mode) causes the violations and diagnostics tables to be dropped also. If the target table is dropped in the restricted mode, the existence of the violations and diagnostics tables causes the DROP TABLE statement to fail.
• Once a violations table is started for a target table, you cannot use the ALTER TABLE statement to add, modify, or drop columns in the target table, violations table, or diagnostics table. Before you can alter any of these tables, you must issue a STOP VIOLATIONS TABLE statement for the target table.
• The database server does not clear out the contents of the violations table before or after it uses the violations table during an Insert, Update, Delete, or SET Database Object Mode operation.
• If a target table has a filtering-mode constraint or unique index defined on it and a violations table associated with it, users cannot insert into the target table by selecting from the violations table. Before you insert rows into the target table by selecting from the violations table, you must take one of the following steps:
• You can set the database object mode of the constraint or unique index to the enabled or disabled database object mode.
• You can issue a STOP VIOLATIONS TABLE statement for the target table.

If it is inconvenient to take either of these steps, but you still want to copy records from the violations table into the target table, a third option is to select from the violations table into a temporary table and then insert the contents of the temporary table into the target table.

• If the target table that is specified in the START VIOLATIONS TABLE statement is fragmented, the violations table has the same fragmentation strategy as the target table. Each fragment of the violations table is stored in the same dbspace as the corresponding fragment of the target table.
• Once a violations table is started for a target table, you cannot use the ALTER FRAGMENT statement to alter the fragmentation strategy of the target table or the violations table.
• If the target table specified in the START VIOLATIONS TABLE statement is not fragmented, the database server places the violations table in the same dbspace as the target table.
• If the target table has BYTE or TEXT columns, BYTE or TEXT data in the violations table is created in the same blobspace as the BYTE or TEXT data in the target table.

To start a violations and diagnostics table for the target table named customer in the demonstration database, enter the following statement:

START VIOLATIONS TABLE FOR customer

Because your START VIOLATIONS statement does not include a USING clause, the violations table is named customer_vio by default. The customer_vio table includes the following columns:

customer_num
fname
lname
company
address1
address2
city
state
zipcode
phone
informix_tupleid
informix_optype
informix_recowner

The customer_vio table has the same table definition as the customer table except that the customer_vio table has three additional columns that contain information about the operation that caused the bad row.

When you issue a START VIOLATIONS TABLE statement for a target table, the diagnostics table that the statement creates has a predefined structure. This structure is independent of the structure of the target table.

The following table shows the structure of the diagnostics table.

When the START VIOLATIONS TABLE statement creates the diagnostics table, the database server uses the set of privileges granted on the target table as a basis for granting privileges on the diagnostics table. However, the database server follows different rules when it grants each type of privilege.

The following table explains the circumstances under which the database server grants each privilege on the diagnostics table. The first column lists each privilege. The second column explains the conditions under which the database server grants that privilege to a user.

The following rules concern privileges on the diagnostics table:

• When the diagnostics table is created, the owner of the target table becomes the owner of the diagnostics table.
• The owner of the diagnostics table automatically receives all table-level privileges on the diagnostics table, including the Alter and References privileges. However, the database server prevents the owner of the diagnostics table from altering the diagnostics table or adding a referential constraint to the diagnostics table.
• You can use the GRANT and REVOKE statements to modify the initial set of privileges on the diagnostics table.
• When you issue an INSERT, DELETE, or UPDATE statement on a target table that has a filtering-mode unique index or constraint defined on it, you must have the Insert privilege on the violations and diagnostics tables.

If you do not have the Insert privilege on the violations and diagnostics tables, the database server executes the INSERT, DELETE, or UPDATE statement on the target table provided that you have the necessary privileges on the target table. The database server does not return an error concerning the lack of insert permission on the violations and diagnostics tables unless an integrity violation is detected during the execution of the INSERT, DELETE, or UPDATE statement.

Similarly, when you issue a SET Database Object Mode statement to set a disabled constraint or disabled unique index to the enabled or filtering mode, and a violations table and diagnostics table exist for the target table, you must have the Insert privilege on the violations and diagnostics tables.

If you do not have the Insert privilege on the violations and diagnostics tables, the database server executes the SET Database Object Mode statement provided that you have the necessary privileges on the target table. The database server does not return an error concerning the lack of insert permission on the violations and diagnostics tables unless an integrity violation is detected during the execution of the SET Database Object Mode statement.

• The grantor of the initial set of privileges on the diagnostics table is the same as the grantor of the privileges on the target table. For example, if the user jenny was granted the Insert privilege on the target table by both the user wayne and the user laurie, both user wayne and user laurie grant the Insert privilege on the diagnostics table to user jenny.
• Once a diagnostics table is started for a target table, revoking a privilege on the target table from a user does not automatically revoke the same privilege on the diagnostics table from that user. Instead you must explicitly revoke the privilege on the diagnostics table from the user.
• If you have fragment-level privileges on the target table, you have the corresponding table-level privileges on the diagnostics table.

The following example illustrates how the initial set of privileges on a diagnostics table is derived from the current set of privileges on the target table.

For example, assume that you have a table called cust_subset that holds customer data. This table consists of the following columns: ssn (social security number), fname (first name), lname (last name), and city (city in which the customer lives).

The following set of privileges exists on the cust_subset table:

• User alvin is the owner of the table.
• User barbara has the Insert and Index privileges on the table. She also has the Select privilege on the ssn and lname columns.
• User carrie has the Update privilege on the city column. She also has the Select privilege on the ssn column.
• User danny has the Alter privilege on the table.

Now user alvin starts a violations table named cust_subset_viols and a diagnostics table named cust_subset_diags for the cust_subset table, as follows:

START VIOLATIONS TABLE FOR cust_subset

	USING cust_subset_viols, cust_subset_diags

The database server grants the following set of initial privileges on the cust_subset_diags diagnostics table:

• User alvin is the owner of the diagnostics table, so he has all table-level privileges on the table.
• User barbara has the Insert, Delete, Select, and Index privileges on the diagnostics table.
• User carrie has the Insert, Delete, Select, and Update privileges on the diagnostics table.
• User danny has no privileges on the diagnostics table.

For information on the relationship between the diagnostics table and the violations table, see Relationship Between the Violations and Diagnostics Tables.

The following issues concern the structure and use of the diagnostics table:

• The MAX ROWS clause of the START VIOLATIONS TABLE statement sets a limit on the number of rows that can be inserted into the diagnostics table when you execute a single statement, such as an INSERT or SET Database Object Mode statement, on the target table.
• The MAX ROWS clause limits the number of rows only for operations in which the table functions as a diagnostics table.
• When a table functions as a diagnostics table, it cannot have triggers or constraints defined on it.
• When a table functions as a diagnostics table, users can create indexes on the table, even though the existence of an index affects performance. You cannot set unique indexes on the diagnostics table to the filtering database object mode.
• If a target table has a violations and diagnostics table associated with it, dropping the target table in the cascade mode (the default mode) causes the violations and diagnostics tables to be dropped also. If the target table is dropped in the restricted mode, the existence of the violations and diagnostics tables causes the DROP TABLE statement to fail.
• Once a violations table is started for a target table, you cannot use the ALTER TABLE statement to add, modify, or drop columns in the target table, violations table, or diagnostics table. Before you can alter any of these tables, you must issue a STOP TABLE VIOLATIONS statement for the target table.
• The database server does not clear out the contents of the diagnostics table before or after it uses the diagnostics table during an Insert, Update, Delete, or Set operation.
• If the target table that is specified in the START VIOLATIONS TABLE statement is fragmented, the diagnostics table is fragmented with a round-robin strategy over the same dbspaces in which the target table is fragmented.

To start a violations and diagnostics table for the target table named stock in the demonstration database, enter the following statement:

START VIOLATIONS TABLE FOR stock

Because your START VIOLATIONS TABLE statement does not include a USING clause, the diagnostics table is named stock_dia by default. The stock_dia table includes the following columns:

informix_tupleid
objtype
objowner
objname

This list of columns shows an important difference between the diagnostics table and violations table for a target table. Whereas the violations table has a matching column for every column in the target table, the columns of the diagnostics table do not match any columns in the target table. The diagnostics table created by any START VIOLATIONS TABLE statement always has the same columns with the same column names and data types.

Related statements: SET Database Object Mode and STOP VIOLATIONS TABLE

For a discussion of object modes and violation detection, see the Informix Guide to SQL: Tutorial.