15.00 - Identity Columns - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Identity Columns

To create a table with an identity column, you must explicitly specify one column in the table definition as either GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY.

You can only define one identity column per table.

Identity columns have many applications, including the automatic generation of unique primary indexes, unique secondary indexes, and primary keys. Values generated for GENERATED ALWAYS AS IDENTITY columns are always unique, but those generated for GENERATED BY DEFAULT AS IDENTITY are only unique if you also specify a UNIQUE constraint on the column. Note that if you load the same row twice into an identity column SET 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.

Do not attempt to update GENERATE ALWAYS identity column values under any circumstances. This operation is not permitted and the system returns an error if you attempt to perform it.

The data type for an identity column is user-defined, but must be an exact numeric type drawn from the following list.

  • BIGINT
  • BYTEINT
  • DECIMAL(n,0)
  • INTEGER
  • NUMBER(n,0)
  • NUMERIC(n,0)
  • SMALLINT
  • In general, you should define the column to be DECIMAL, NUMBER, or NUMERIC, with the value for precision, n, being the largest number available on your system.

    The largest size identity column Teradata Database produces is that stipulated by a DECIMAL(18,0), a NUMBER(18,0), or a NUMERIC(18,0) specification. 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 BIGINT type, without the CREATE TABLE or ALTER TABLE request aborting, but the values generated by the identity column feature remain limited to the DECIMAL(18,0) type and size.

    Rows are processed in different orders on different systems under Unity Director, and there is no guarantee that the same data row will be assigned the same identity column 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 a table.

    If you must drop the IDENTITY attribute from an identity column to use the Unity Director product with bulk data loads, you can use an ALTER TABLE request to do so. See SQL Data Definition Language for more information about ALTER TABLE and identity columns.