Show TOC

Background documentationJoin View Table, QUERY Expression, CONSTRAINT Definition, Trigger in INSERT Statement

 

The following lists some of the special features of INSERT statements.

Structure

Join View Table in an INSERT Statement

  • If the table name in an INSERT statement does not identify a join view table, and if a row containing the key of the row to be inserted already exists, the result will depend on the DUPLICATES clause. The INSERT statement will fail if no DUPLICATES clause is specified.

  • If the table name identifies a join view table, a row is inserted into each base table on which the view table is based.

    If the key table of the view table already contains a row with the key of the row to be inserted, the INSERT statement will fail.

    If any row in a base table, which is not the key table of the view table, already contains the key of the row to be inserted, the INSERT statement will fail if the row to be inserted does not match the existing row.

QUERY Expression in an INSERT Statement

  • If a QUERY expression is specified in the INSERT statement, the specified table must not be a join view table. The QUERY expression defines a result table whose ith column is assigned to the ith target column. A row is formed from each row in the result table and inserted in the base table.

    Each of these rows has the following contents: Each base table column that is a target column of the INSERT statement contains the value of the corresponding column in the current result table row.

  • If a QUERY expression is not specified in the INSERT statement, exactly one row is inserted in the specified table.

    The inserted row has the following contents: Each base table column that is a target column of the INSERT statement contains the value assigned to the respective target column.

The following still applies to the inserted row(s):

  • All columns of the base table that are not target columns of the INSERT statement and for which a DEFAULT specification exists contain the DEFAULT value.

  • All columns of the base table that are not target columns of the INSERT statement and for which a DEFAULT specification exists contain the NULL value.

CONSTRAINT Definition in an INSERT Statement

If CONSTRAINT definitions exist for base tables in which rows are to be inserted with the INSERT statement, each row that is to be inserted is checked to determine whether it fulfills the CONSTRAINT definition. The INSERT statement fails if this is not the case for at least one row.

If at least one of the base tables in which rows are to be inserted with the INSERT statement is the referencing table of a referential CONSTRAINT definition, the database system checks each row to be inserted to determine whether the foreign key resulting from the row exists as a key or as a value of an index defined with UNIQUE in the corresponding referenced table (referenced_table). The INSERT statement fails if this is not the case for at least one row.

Triggers in an INSERT Statement

If triggers that are to be executed after an INSERT statement were defined for base tables in which rows are to be inserted with the INSERT statement, they are executed accordingly. The INSERT statement will fail if one of these triggers fails.

More Information

CREATE VIEW Statement (create_view_statement)