1
Download Informix Guide to SQL: Syntax
Transcript
SET ■ 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. 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. 1-514 Informix Guide to SQL: Syntax
