Teradata Identity Column

Teradata Identity Column

Identity column is a way to generate system generated unique identifier for a table in Teradata.



➠ Characterstics for Identity Columns



➠ Valid datatypes for identity columns



➠ Syntax for Identity columns

BIGINT GENERATED [ ALWAYS | BY DEFAULT] AS IDENTITY
    (
     START WITH [start_vale] 
     INCREMENT BY [increment_by_value]
     MAXVALUE [Maximum value ]
     [NO CYCLE|CYCLE]
     )
Notes:
  • If sequence is defined as 'GENERATED ALWAYS' then Teradata will replaces user defined insert values with system‑generated values.
  • If sequence is defined as 'GENERATED BY DEFAULT' then Teradata will accept user defined insert values (which may induce duplicate keys unless otherwise constrained with UPI, USI or PK).
  • By default sequence will start with '1' if not specified in the DDL definition.
  • By default minimum value will be the lowest value that underlying datatype can store. For example INTEGER, it will be -2147483647.
  • By default maximun value will be the highest value that underlying datatype can store. For example INTEGER, it will be 2147483647.
  • Maximum value that Teradata can generate is 999,999,999,999,999,999 which is equivalent to DECIMAL(18) or Number(18). If you user will try to set max value beyond the teradata supported max value then it will fail with below error.
    *** Failure 5785 Invalid value for Identity Column parameter MAXVALUE.
    
  • By default sequence will be incremented by '1' if not specified in the DDL definition.
  • By default sequence will be NO cycle if not specified in the DDL definition.
  • When sequence is NO CYCLE and all the sequence values are already exhausted then Teradata will throw below error.
    *** Failure 7545 Numbering for Identity Column tablename.identyColumnName is over its limit.
    


➠ Examples of tables with Identity Columns




➠ Limitations of tables with Identity Column