Teradata Identity Column

Pradeep

Teradata Identity Column

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

Characteristics for Identity Columns
Valid datatypes for identity columns
Syntax for Identity columns
Examples of tables with Identity Columns
Limitations of tables with Identity Column

➠ Characteristics for Identity Columns

Identity column is not a single sequence but multiple parallel sequences(one on each AMP).
System generated number in Teradata may have gaps when using identity column because number is generated from multiple AMPs.

Definition of each identity column is stored in 'dbc.idcol' data dictionary table. Teradata (PE & AMPs) also use this table to get next available range once the previously cached range values are exhausted after being allocated to rows.


SELECT StartValue,
       MinValue,
       MaxValue,
       Increment,
       Cyc,
       AvailValue,
       CAST(dbase.databasename AS VARCHAR(35)) databasename,
       CAST(dbc.tvm.tvmname AS VARCHAR(35)) tablename
FROM dbc.idcol
  INNER JOIN dbc.tvm
          ON (dbc.idcol.databaseid = dbc.tvm.databaseid
         AND dbc.idcol.tableid = dbc.tvm.tvmid)
  INNER JOIN dbc.dbase ON tvm.DatabaseId = dbase.DatabaseId
WHERE dbc.tvm.tvmname = '[table_name]'
AND   dbase.databasename = '[database_name]';

Surrogate key is the Unique number generated(not related to any other column) for each row in a table, just to distinguish each row uniquely. Check Surrogate Key page to understand the concept of surrogate key.

➠ Valid datatypes for identity columns

BYTEINT
SMALLINT
INTEGER
DECIMAL(n,0)
NUMBER(n,0)
BIGINT

➠ 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

Example 1: Identity Column defined with no attributes


CREATE MULTISET TABLE tutorial_db.employee_id_column1
     (
      emp_no BIGINT GENERATED ALWAYS AS IDENTITY,
      emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC
      )
PRIMARY INDEX ( emp_name );


 START WITH 1 
 INCREMENT BY 1 
 MINVALUE -999999999999999999 
 MAXVALUE 999999999999999999 
 NO CYCLE

Example 2: Identity Column defined with required attributes, values will not be cycled


CREATE MULTISET TABLE tutorial_db.employee_id_column
     (
      emp_no INTEGER GENERATED ALWAYS AS IDENTITY
      (
         START WITH 1 
         INCREMENT BY 1 
         MAXVALUE 2000000000 
         NO CYCLE
      ),
      emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC
      )
PRIMARY INDEX ( emp_name );

Example 3: Identity Column defined with required attributes, values will be cycled


CREATE MULTISET TABLE tutorial_db.employee_id_column
     (
      emp_no INTEGER GENERATED ALWAYS AS IDENTITY
      (
         START WITH 1 
         INCREMENT BY 1 
         MAXVALUE 2000000000 
         CYCLE
      ),
      emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC
      )
PRIMARY INDEX ( emp_name );

➠ Limitations of tables with Identity Column

Even though user defines BIGINT, DECIMAL(38) or Number(38) for identity column, system generated value cannot exceed maximum value(999999999999999999).
Only 1 identity column per table can be defined. If users will try to create multiple Identity columns then Teradata will throw below error.
```
*** Failure 3706 Syntax error: A table may not have more than one identity column.
```
Identity column cannot be defined in Non-partitioned NoPI tables (supported for column‑partitioned NoPI tables). If user tries to define then ddl command will fail with below error.
```
*** Failure 3706 Syntax error: Identity Column in a NoPI table is unsupported.
```
Hash Index, Join Index, composite Primary Index and composite Secondary Index cannot be created on Identity column. If users will try to create then Teradata will throw below error.
```
*** Failure 5784 Illegal usage of Identity Column emp_no.
```
Teradata does not support Value Ordered Secondary Index on the datatypes which are bigger than 4 bytes(such bigint). It will throw below error if user tries .
```
*** Failure 5466 Error in Secondary Index DDL, Order by column is non-numeric, or is more than 4 bytes.
```