Duplicate Row Checks - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Duplicate Row Checks

Unless a table is created as MULTISET (and without UNIQUE constraints) to allow duplicate rows, the system always checks for duplicate rows during the update process. The order in which updates are executed can affect the result of a transaction.

Consider the following example:

     CREATE SET TABLE t1 (
       a INTEGER, 
       b INTEGER) 
     PRIMARY INDEX (a);
 
     INSERT INTO t1 VALUES (1,1);
 
     INSERT INTO t1 VALUES (1,2);
 
     UPDATE t1 
     SET b = b + 1 
     WHERE a = 1; /* fails */
 
     UPDATE t1 
     SET b = b - 1 
     WHERE a = 1; /* succeeds */

The first UPDATE request fails because it creates a duplicate row.

If the order of the UPDATE requests is reversed, then both UPDATE requests succeed because the UPDATE does not result in duplicate rows.

     CREATE SET TABLE t1 (
       a INTEGER, 
       b INTEGER) 
     PRIMARY INDEX (a);
 
     INSERT INTO t1 VALUES (1,1);
 
     INSERT INTO t1 VALUES (1,2);
 
     UPDATE t1 
     SET b = b - 1 
     WHERE a = 1; /* succeeds */
 
     UPDATE t1 
     SET b = b + 1 
     WHERE a = 1; /* succeeds */

This mode is characteristic of both simple and join updates. Updates that affect primary or secondary index values, on the other hand, are implemented as discrete delete and insert operations.