16.10 - Primary Index Assignment - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)

In general, most Teradata Database tables require a primary index. (Some tables in the Data Dictionary do not have primary indexes and a global temporary trace table is not allowed to have a primary index.) To create a primary index, use the CREATE TABLE statement.

If you do not assign a primary index explicitly when you create a table, Teradata Database assigns a primary index, based on the following rules.

Primary Index Primary 
Key Unique Column Constraint Teradata Database selects the …
no YES no primary key column set to be a UPI.
no no YES first column or columns having a UNIQUE constraint to be a UPI.
no YES YES primary key column set to be a UPI.
no no no first column defined for the table to be a NUPI.

If the data type of the first column in the table is a LOB, then the CREATE TABLE operation aborts and the system returns an error message.

In general, the best practice is to specify a primary index instead of having Teradata Database select a default primary index.