The following rules and restrictions apply to specifying identity columns as part of a table definition.
- Only one 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 a nonpartitioned 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 Teradata Unity and that will be loaded using bulk data load utilities because Vantage 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
- 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. For the ANSI SQL:2011 definition of an exact numeric data type, see Teradata Vantage™ - Database Design, B035-1094 or Teradata Vantage™ - Data Types and Literals, B035-1143.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
Table Type Identity Column Description 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 Vantage 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.
Identity Column Action for 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.
To ensure 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