17.05 - Duplicate Row Checks - 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)

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.