15.00 - Identity Columns - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Identity Columns

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 Unity Director. Unity Director provides its own mechanism for generating deterministic unique values that do not present the problems that identity columns do for managing multiple Teradata Database instances. See Teradata Unity Installation Guide and Teradata Unity User Guide.

Note: 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” on page 546 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 the Unity Director product. See Teradata Unity Installation and User Guide for details.
  • Support the following bulk insert operations only.
  • Single statement INSERT requests through multiple concurrent sessions.
  • For example, parallel BTEQ imports into the same table.

  • Multistatement INSERT requests through multiple concurrent sessions.
  • For example, Teradata Parallel Data Pump inserts.

  • INSERT … SELECT requests.
  • 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 request 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 (see SQL Data Types and Literals and Utilities: Volume 1 (A-K)). 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 request aborting, or even 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 requests 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 requests 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 both 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 an unpartitioned 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.
  • Teradata Database generates values for the column when a new row is inserted into the table except in certain cases described later.
  •  

    IF you specify GENERATED …

    THEN the system …

    ALWAYS

    always generates a unique value for the column when Teradata Database 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. This means that 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 Teradata Database inserts a new row into the table only if the INSERT request 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.

     

    IF you are using the identity column for this purpose …

    THEN you should specify this option …

    to ensure a UPI, USI, PK, or some other row uniqueness property

    ALWAYS … NO CYCLE.

  • to load data into or unload data from a table
  • to copy rows from one table to another table
  • to fill in gaps in the sequence
  • to reuse numbers that once belonged to now-deleted rows
  • BY DEFAULT.

  • Unity Director does not support identity columns for bulk data loads because Teradata Database processes rows in different orders on different systems. Because of this, it is not possible to guarantee that the same data row is assigned the same identity value on all systems. Furthermore, it is not possible to assign Unity Director‑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 Unity Director, you can use an ALTER TABLE request to drop the identity attribute from an identity column without dropping the column itself or its data. See “ALTER TABLE” in SQL Data Definition Language Syntax and Examples. For more information, see Teradata Unity Installation Guide and Teradata Unity User Guide.