1.1 - 8.10 - New Table - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

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