17.10 - Primary Index Assignment - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Fundamentals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

In general, most 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, the database assigns a primary index, based on the following rules.

Primary Index Primary Key Unique Column Constraint The 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 the database select a default primary index.