Identity Columns - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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.

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.
  • 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