17.10 - Identity Columns - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

Identity columns are used mainly to ensure row uniqueness by taking a system-generated unique value. They are valuable for generating simple unique indexes and primary and surrogate keys when composite indexes or keys are not desired.

Identity columns are also useful for ensuring column uniqueness when merging several tables or to avoid significant preprocessing when loading and unloading tables.

You should not create an identity column for a table that you intend to use with Teradata Unity. Teradata Unity provides its own mechanism for generating deterministic unique values that do not present the problems that identity columns do for managing multiple database instances. See the Teradata Unity documentation.

Loading a row from a client system that duplicates an existing row in an identity column table is permitted because the assignment of the identity column to the row makes it unique. If this presents a problem, you must filter your data for duplicates before you load it into an identity column table. See Identity Columns, Duplicate Column Values, and Duplicate Rows for further duplicate value and duplicate row issues for identity column tables.

The various parameters for identity columns are maintained by the DBC.IdCol system table.

Identity columns have the following properties.
  • Begin with the value 1 unless a different START WITH value is specified.
  • Increment by 1 unless a different INCREMENT BY value is specified.

    Can decrement if a negative INCREMENT BY value is specified.

  • Can specify maximum values for incrementing or minimum values for decrementing a value series.
  • Values can be recycled.
Identity columns have the following rules and restrictions.
  • Cannot be used for tables that are managed by Teradata Unity.
  • Support the following bulk insert operations only.
    • 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.
  • If a triggered event is an INSERT into a table with an identity column, then the triggered action statement block or the WHEN clause of the trigger cannot reference the identity column.
  • GENERATED ALWAYS columns cannot be updated.
  • GENERATED ALWAYS column values that also specify NO CYCLE are always unique.
  • GENERATED BY DEFAULT columns generate a value only when the column is set null by an INSERT statement in one of the following ways.
    • 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.

  • GENERATED BY DEFAULT columns can be updated.
  • GENERATED BY DEFAULT column values are not guaranteed to be unique.

    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.
  • Identity columns cannot have a UDT, Geospatial, ARRAY, VARRAY, or Period data type.
  • GENERATED ALWAYS identity columns cannot be null.
  • The cardinality of a table with unique identity column values is limited by the maximum value for the identity column data type.

    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.

  • Inserts into GENERATED BY DEFAULT identity columns using Teradata Parallel Data Pump cannot reuse Teradata Parallel Data Pump field variables in another parameter of the same insert operation.

    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));
  • INSERT operations into GENERATED BY DEFAULT identity columns using Teradata Parallel Data Pump field variables cannot specify field variables if the value inserted into the identity column is derived from an expression that includes a Teradata Parallel Data Pump field variable.
  • INSERT operations into GENERATED BY DEFAULT and GENERATED ALWAYS identity columns using Teradata Parallel Data Pump field variables cannot insert into the identity column of more than 1 identity column table.
  • You cannot specify an identity column for a nonpartitioned NoPI table, but you can specify an identity column for a column-partitioned table.
The GENERATED … AS IDENTITY keywords introduce a clause that specifies the following.
  • The column is an identity column.
  • Vantage generates values for the column when a new row is inserted into the table except in certain cases described later.
GENERATED Description
ALWAYS Always generates a unique value for the column when Vantage inserts a new row into the table and NO CYCLE is specified.

If you load the same row twice into an identity column table, it is not rejected as a duplicate because it is made unique as soon as an identity column value is generated for it. Some preprocessing must still be performed on rows to be loaded into identity column tables if real world uniqueness is a concern.

BY DEFAULT Generates a unique value for the column when Vantage inserts a new row into the table only if the INSERT statement does not specify a value for the column.

The generated value is guaranteed to be unique within the set of generated values only if you specify the NO CYCLE option.

Identity Column Purpose Option
Ensure a UPI, USI, PK, or some other row uniqueness property. ALWAYS … NO CYCLE
  • Load data into or unload data from a table.
  • Copy rows from one table to another table.
  • Fill in gaps in the sequence.
  • Reuse numbers that once belonged to now-deleted rows.
BY DEFAULT
  • Teradata Unity does not support identity columns for bulk data loads because Vantage processes rows in different orders on different systems. It is not possible to guarantee that the same data row is assigned the same identity value on all systems. Also, it is not possible to assign Teradata Unity-determined values to specific rows across systems, especially when the identity column is the primary index for the table.

    If your system has existing tables with identity columns that must run under Teradata Unity, you can use an ALTER TABLE statement to drop the identity attribute from an identity column without dropping the column itself or its data. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.