Identity Columns - Advanced SQL Engine - Teradata Database

Can decrement if a negative INCREMENT BY value is specified.

• Single statement INSERT statements through multiple concurrent sessions.

  For example, parallel BTEQ imports into the same table.

• Multistatement INSERT statements through multiple concurrent sessions.

  For example, Teradata Parallel Data Pump inserts.

• INSERT … SELECT statements.

• Explicit specification of NULL in the multivalue.

  For example, INSERT INTO table VALUES (1,NULL);

• Implicit specification of NULL by the omission of a value in the multivalue when no column name list is provided.

  For example, INSERT INTO table VALUES (1,);

• Omission of a column name from the column name list.

  For example, INSERT INTO table (x) VALUES (1);

  where table has more than a single column.

You must comply with all of the following restrictions if you want to guarantee the uniqueness of GENERATED BY DEFAULT column values.

• You must specify NO CYCLE.
• Any user-specified values you specify must be outside the range of any system-generated values.
• You must enforce the uniqueness of the user-specified values yourself.

Because of this, you should specify a numeric type that ensures the largest possible number of unique values for the identity column can be generated.

The maximum numeric data type ranges are DECIMAL(18,0), NUMERIC(18,0), and exact NUMBER(18,0), or approximately 1 x 1018 rows. You cannot use approximate NUMBER columns for identity columns.

This is true even when the DBS Control parameter MaxDecimal is set to 38. For more information, see Teradata Vantage™ - Data Types and Literals, B035-1143 and Teradata Vantage™ - Database Utilities , B035-1102 . You can define an identity column with more than 18 digits of precision, or even as a large fixed NUMBER or BIGINT type, without the CREATE TABLE or ALTER TABLE statement returning a warning message, but the values generated by the identity column feature remain limited to the DECIMAL(18,0) type and size.

The following examples are based on this table definition.

    CREATE MULTISET TABLE test01 (
      a1 INTEGER GENERATED BY DEFAULT AS IDENTITY
         (START WITH 1
          INCREMENT BY 20
          MAXVALUE 1000
         ),
      a2 INTEGER);
       

The following simple INSERT statements fail because they reuse Teradata Parallel Data Pump field variables.

    .LAYOUT layoutname;
    .FIELD uc1 INTEGER;
    .FIELD uc2 INTEGER;
    .DML LABEL labelname;

    INSERT INTO test01 VALUES (:uc1, :uc1);
       
    .LAYOUT layoutname;
    .FIELD uc1 INTEGER;
    .FIELD uc2 INTEGER;
    .DML LABEL labelname;

    INSERT INTO test01 VALUES (:uc2, (:uc2 + :uc2));

The following simple INSERT statements succeed because they do not reuse Teradata Parallel Data Pump field variables.

    .LAYOUT layoutname;
    .FIELD uc1 INTEGER;
    .FIELD uc2 INTEGER;
    .DML LABEL labelname;

    INSERT INTO test01 VALUES (:uc1, :uc2);
       
    .LAYOUT layoutname;
    .FIELD uc1 INTEGER;
    .FIELD uc2 INTEGER;
    .DML LABEL labelname;

    INSERT INTO test01 VALUES (:uc2, (:uc1 + :uc1));