START VIOLATIONS TABLE statement

Use the START VIOLATIONS TABLE statement to create a violations table and a diagnostics table for a specified target table. The START VIOLATIONS TABLE statement is an extension to the ANSI/ISO standard for SQL.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-START VIOLATIONS TABLE FOR--+--------------+--table---------->
                               '- 'owner' --.-'          

>--+-----------------------------------+------------------------>
   '-USING--violations--,--diagnostics-'   

>--+--------------------+--------------------------------------><
   '-MAX ROWS--num_rows-'   

Element Description Restrictions Syntax
diagnostics Declares the name of a diagnostics table to be associated with the target table. Default name is table_dia. Must be unique among names of tables, views, sequences, and synonyms Identifier
num_rows Maximum number of rows that the database server can insert into violations when a single statement is executed on table Must be an integer in range from 1 to the maximum value of the INTEGER data type Literal Number
owner The owner of table Must own the table Owner name
table Target table for which violations and diagnostics tables are to be created If USING clause is omitted, no more than 124 bytes Identifier
violations Violations table to be associated with table. Default name is table_vio. Same restrictions as diagnostics Identifier

Usage

The database server associates the violations table and the diagnostics table) with the target table that you specify after the FOR keyword by recording the relationship among the three tables in the sysviolations system catalog table.

A target table must satisfy these requirements:
  • It cannot be a table in a database that is not the current database.
  • It cannot be an object that the CREATE EXTERNAL TABLE statement defined.
  • It cannot already be associated with a violations or diagnostics table.
  • It cannot be a system catalog table.

The START VIOLATIONS TABLE statement creates the special violations table that holds nonconforming 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 that each row causes in the violations table.


Copyright© 2019 HCL Technologies Limited