Unique Identifier Column for New Table | Teradata Vantage - New Table - Teradata Vantage

When you create a table, the easiest way to give it a unique identifier column is to specify the IDENTITY clause with the options GENERATED ALWAYS or GENERATED BY DEFAULT and NO CYCLE. For more information about identity columns, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

Example: Create Table with INTEGER Identity Column

CREATE MULTISET TABLE test01 (
  a1 INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1 NO CYCLE),
  a2 INTEGER
);

INSERT INTO test01 VALUES (,5);
INSERT INTO test01 VALUES (NULL,4);

-- For following line, system overwrites 1 with a system-generated value
INSERT INTO test01 VALUES (1,6);
 
INSERT INTO test01(a2) VALUES (8);

-- for the line below, system will overwrite 11 with a system generated value
INSERT INTO test01 VALUES (11,22); 

SELECT * FROM test01;
 
         a1           a2
-----------  -----------
          3            6
          4            8
          1            5
          2            4
          5           22

Example: Create Table with NUMERIC(18,0) Identity Column

For an extremely large table, the recommended data type for the identity column is NUMERIC(18,0), which accommodates 1018 rows.

Example: Identity Column for Maximum Supported Value

CREATE MULTISET TABLE test02 (
  a1 NUMERIC(18,0) GENERATED ALWAYS AS IDENTITY(START WITH 1 NO CYCLE),
  a2 INTEGER
);

Example: Create Table with User-Populated Identity Column

To create a table with an identity column that the user must populate, specify that the identity column is GENERATED BY DEFAULT. To avoid duplication, make the identity column the UNIQUE PRIMARY INDEX.

CREATE MULTISET TABLE test03 (
  a1 INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1 NO CYCLE),
  a2 INTEGER
) UNIQUE PRIMARY INDEX (a1);

INSERT INTO test03 VALUES (,5);
INSERT INTO test03 VALUES (null,4);
INSERT INTO test03 VALUES (1,6); -- error thrown because value is not unique.
INSERT INTO test03(a2) VALUES (8);
INSERT INTO test03 VALUES (11,22);
 
SELECT * FROM test03;
         a1           a2
-----------  -----------
          3            8
          1            5
          2            4
         11           22