Identity Columns - Teradata Vantage

Identity columns generate simple unique indexes.

Can decrement if a negative INCREMENT BY value is specified.

• Single statement INSERT statements through multiple concurrent sessions.

  For example, parallel BTEQ imports into the same table.

• Multiple-statement INSERT statements through multiple concurrent sessions.

  For example, Teradata Parallel Data Pump inserts.

• INSERT … SELECT statements.

• Explicit specification of NULL in the multivalue.

  For example, INSERT INTO table VALUES (1,NULL);

• Implicit specification of NULL by the omission of a value in the multivalue when no column name list is provided.

  For example, INSERT INTO table VALUES (1,);

• Omission of a column name from the column name list.

  For example, INSERT INTO table (x) VALUES (1);

  where table has more than a single 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.

Therefore, specify a numeric type that makes sure the largest possible number of unique values for the identity column can be generated.

The maximum numeric data type ranges are DECIMAL(18,0), NUMERIC(18,0), and exact NUMBER(18,0), or approximately 1 x 1018 rows. You cannot use approximate NUMBER columns for identity columns.

You can define an identity column with more than 18 digits of precision, or even as a large fixed NUMBER or BIGINT type, without the CREATE TABLE or ALTER TABLE statement returning a warning message, but the values generated by the identity column feature remain limited to the DECIMAL(18,0) type and size.

The following examples are based on this table definition.

CREATE MULTISET TABLE test01 (
  a1 INTEGER GENERATED BY DEFAULT AS IDENTITY
     (START WITH 1
      INCREMENT BY 20
      MAXVALUE 1000
     ),
  a2 INTEGER);

The following INSERT statements reuse Teradata Parallel Data Pump field variables, and therefore fail.

.LAYOUT layoutname;
.FIELD uc1 INTEGER;
.FIELD uc2 INTEGER;
.DML LABEL labelname;

INSERT INTO test01 VALUES (:uc1, :uc1);
       
.LAYOUT layoutname;
.FIELD uc1 INTEGER;
.FIELD uc2 INTEGER;
.DML LABEL labelname;

INSERT INTO test01 VALUES (:uc2, (:uc2 + :uc2));

The following INSERT statements do not reuse Teradata Parallel Data Pump field variables, and therefore succeed.

.LAYOUT layoutname;
.FIELD uc1 INTEGER;
.FIELD uc2 INTEGER;
.DML LABEL labelname;

INSERT INTO test01 VALUES (:uc1, :uc2);
       
.LAYOUT layoutname;
.FIELD uc1 INTEGER;
.FIELD uc2 INTEGER;
.DML LABEL labelname;

INSERT INTO test01 VALUES (:uc2, (:uc1 + :uc1));