15.00 - Rules for Specifying Identity Columns - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Rules for Specifying Identity Columns

The following rules and restrictions apply to specifying identity columns as part of a table definition.

  • Only 1 identity column can be specified per table.
  • An identity column need not be the first column defined for a table.
  • An identity column need not be a primary index column.
  • For example, a NUPI table can also have an identity column.

  • You cannot specify an identity column for an unpartitioned NoPI table.
  • You can specify an identity column for a column‑partitioned table.
  • Do not specify an identity column for a table that will be under the control of Unity Director and that will be loaded using bulk data load utilities 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.
  • An identity column cannot be part of any of the following types of index.
  • Composite primary index
  • Composite secondary index
  • Partitioned primary index
  • Join index
  • Hash index
  • Value-ordered index
  • You cannot perform atomic upsert operations on tables with an identity column as their primary index.
  • An identity column must be defined with an exact numeric data type (see Database Design or SQL Data Types and Literals for the ANSI SQL:2011 definition of an exact numeric data type).
  • Note: Even when the data type for an identity column is specified as BIGINT, the largest identity column value the system generates is restricted to a size that can be accommodated by a DECIMAL(18,0), NUMBER(18,0), or NUMERIC(18,0) data type.

    The following are examples of valid numeric data types for identity columns.

  • BYTEINT
  • DECIMAL(n,0)
  • INTEGER
  • NUMERIC(n,0)
  • NUMBER(n,0)
  • SMALLINT
  • BIGINT
  • The following table lists the MAXVALUE and MINVALUE defaults for various identity column data types.

     

    Data Type

    Default MAXVALUE

    Default MINVALUE

    DECIMAL(1,0)
    NUMERIC(1,0)

                                                 9

                                               -9

    DECIMAL(2,0)
    NUMERIC(2,0)

                                               99

                                             -99

    ...

    ...

    ...

    DECIMAL(18,0)
    NUMERIC(18,0)
    NUMBER(18,0)

    Even when the value for the DBS Control parameter MaxDecimal is set to 38, the largest identity column value the system generates is restricted to a size that can be accommodated by any of the following data types.

  • DECIMAL(18,0)
  • NUMBER(18,0)
  • NUMERIC(18,0)
  •      999,999,999,999,999,999

      -999,999,999,999,999,999

    BYTEINT

                                             127

                                          -127

    SMALLINT

                                       32,767

                                     -32,767

    INTEGER

                           2,147,483,647

                         -2,147,483,647

    BIGINT

    Even when the data type for an identity column is specified as BIGINT, the largest identity column value the system generates is restricted to a size that can be accommodated by a DECIMAL(18,0), NUMBER(18,0), or NUMERIC(18,0) data type.

     9,223,372,036,854,775,807

    -9,223,372,036,854,775,807

    The following are examples of non‑valid data types for identity columns.

  • All non-numeric types, including DateTime, INTERVAL, Period, Geospatial, UDT, ARRAY, VARRAY, BLOB, and CLOB types. This is true even if the UDT is based on 1 (distinct UDT) or several (structured UDT) of the valid exact numeric types.
  • DECIMAL(n,m) where m is not 0
  • DOUBLE PRECISION
  • FLOAT
  • NUMERIC(n,m) where m is not 0
  • NUMBER (approximate type)
  • REAL
  •  

    WHEN this type of table …

    HAS an identity column specified to be …

    THEN it …

    MULTISET

    GENERATED ALWAYS

    cannot have duplicate rows.

    SET

  • GENERATED ALWAYS
  • GENERATED BY DEFAULT
  • MULTISET

    GENERATED BY DEFAULT

    can have duplicate rows.

  • If you insert values into a GENERATED BY DEFAULT IDENTITY column SET table, the inserted row (but not the inserted value for the identity column) must be unique or the system does not accept it.
  • If you insert values into a GENERATED BY DEFAULT IDENTITY column in a MULTISET table, and those values must always be unique, then you must explicitly constrain the column to be unique by specifying it to be a UPI, a USI, a PRIMARY KEY, or UNIQUE. Because Teradata Database treats nulls in this case as if they were all equal to one another, you can only insert 1 null into a column constrained to be UNIQUE. Because of this, it is usually considered to be preferable to define any such column to be both UNIQUE and NOT NULL.
  •  

    A column specified to be …

    Has this response to user-defined insert values …

    GENERATED ALWAYS

    replaces them with system‑generated identity values.

    GENERATED BY DEFAULT

    accepts them unless otherwise constrained.

    A column specified to be GENERATED BY DEFAULT AS IDENTITY with NO CYCLE only generates a unique column value for inserted rows that contain a null identity column.

    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.
  • You cannot specify any of the following column attributes for an identity column.
  • DEFAULT
  • BETWEEN
  • COMPRESS
  • CHECK constraints
  • FOREIGN KEY REFERENCES constraints