Duplicate Row Checks - Teradata Database - Teradata Vantage NewSQL Engine

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.