Relationships Among the Target, Violations, and Diagnostics Tables

Users can take advantage of the relationships among the target, violations, and diagnostics tables to obtain diagnostic information about rows that cause 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.

• One row in the violations table is a copy of any row in the target table for which a data-integrity violation was detected. A row in the diagnostics table contains information about the nature of the data-integrity violation caused by the nonconforming row in the violations table.
• One row in the violations table has a unique serial identifier in the informix_tupleid column. A 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 nonconforming row in the violations table can cause more than one data-integrity violation.

For example, the same nonconforming row can violate a unique index for one column, a not-NULL constraint for another column, and a check constraint for a third column. In this case, the diagnostics table contains three rows for the single nonconforming 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 diagnostic information about any or all nonconforming rows in the violations table. SELECT statements can perform these joins interactively, or you can write a program to perform them within transactions.