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