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 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.
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:
  • 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.

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.