17.05 - Duplicate Rows and INSERT - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

When an insert operation would create a duplicate row, the outcome of the operation depends on how the table is defined. The system ignores trailing pad characters in character strings when comparing values for field or row duplication.

Table Definition Duplicate Row Insert Action
MULTISET with no UNIQUE constraints Inserted.
  • SET
  • MULTISET with UNIQUE constraints
Not inserted.

An error is returned to the requestor.

If inserting into an identity column table, this is true only when the column is defined WITH NO CYCLE. An error can occur in this case if an attempt is made to cycle. The system can also return errors for other reasons, like uniqueness violations, before a duplicate row violation can be reported.

Duplicate Rows and INSERT … SELECT

If an INSERT using a SELECT subquery will create duplicate rows, the result depends on the table definition:
  • Duplicate rows are permitted in a MULTISET set only if no UNIQUE constraints or UNIQUE indexes are defined on the table.
  • Duplicate rows are not permitted in a SET table.

The following table summarizes the restrictions on duplicate rows and INSERT … SELECT:

Type of Table Duplicate Rows
MULTISET with no unique constraints Permitted.

Inserted duplicate rows are stored in the table.

MULTISET with unique constraints not permitted. An error message is returned to the requestor.

The following constraints are considered to be unique in Vantage:

  • Unique primary index
  • Unique secondary index
  • Primary key
  • UNIQUE column constraint
  • GENERATED ALWAYS identity column constraint.

For nontemporal tables, unique secondary indexes, primary keys, and UNIQUE constraints are all implemented internally as unique secondary indexes.

For information about how USIs, PKs, and UNIQUE constraints are implemented for temporal tables, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

SET Not permitted.
  • In ANSI session mode, the system rejects the request and returns an error message to the requestor.

    If some other error, such as violating a uniqueness constraint, occurs first, then the system returns that error to the requestor rather than a duplicate row message

  • In Teradata session mode, the database does the following.

    Rejects the duplicate rows in the transaction.

    Inserts the non-duplicate rows into the table.

    Does not return an error message to the requestor.

In Teradata session mode, if an INSERT … SELECT request specifies a SAMPLE clause that selects a set of rows from a source MULTISET table, and then inserts them into a target SET table, and the sampled row set contains duplicates, the number of rows inserted into the target SET table might be fewer than the number requested in the SAMPLE clause.

In Teradata session mode, the condition occurs because SET tables reject attempts to insert duplicate rows into them. The result is that the INSERT portion of the INSERT … SELECT operation inserts only distinct rows into SET target tables. As a result, the number of rows inserted into the target table can be fewer than the number specified in the SAMPLE clause.

For example, if an INSERT … SELECT request SAMPLE clause requests a sample size of 10 rows, and there are duplicate rows from the MULTISET source table in the collected sample, the system rejects the duplicate instances when it attempts to insert the sampled rows into the SET table and inserts only the distinct rows from the sample set. That is, you could request a sample of 10 rows, but the actual number of rows inserted into the target table could be fewer than 10 if there are duplicate rows in the sampled row set. The database does not return any warning or information message when this condition occurs.