Primary Index Properties
Teradata Database uses the primary index of a table to control its row distribution
and retrieval using the Teradata Database hashing algorithm (see “Teradata Database Hashing Algorithm” on page 225).
The primary index for an unpopulated table is defined using the CREATE TABLE data
definition statement (see the documentation for the CREATE TABLE, CREATE HASH INDEX,
and CREATE JOIN INDEX statements in SQL Data Definition Language).
CREATE INDEX is used only to create secondary indexes.
The primary index for an empty table can be modified, with restrictions, using the
ALTER TABLE data definition statement (see the documentation for the ALTER TABLE statement
in SQL Data Definition Language)
If no explicit primary index is defined in a CREATE TABLE request, the system determines
whether the table has no primary index or to assign one automatically according to
the rules described in “Primary Index Defaults” on page 263.
A primary index can be unique or nonunique (see “Unique Primary Indexes” and “Nonunique Primary Indexes” on page 265).
If the primary index is not defined explicitly as unique, then its definition defaults
to nonunique (see “Primary Index Defaults” on page 263).
A primary index can be row‑partitioned or nonpartitioned (see “Row-partitioned Primary Indexes” and “Nonpartitioned Primary Indexes” on page 267).
If row‑partitioned, a primary index can be partitioned at a single level or at multiple
levels (see “Single‑Level Partitioning” on page 362 “Multilevel Partitioning” on page 372).
A primary index of a hash or join index can also be value‑ordered, subject to constraints
on the data type and field length of the ordering column. See the documentation for
CREATE HASH INDEX and CREATE JOIN INDEX in SQL Data Definition Language for details.
A primary index can be composed of as many as 64 columns (see “Restrictions on Primary Indexes” on page 262).
The values for a primary index can be generated automatically if defined on an identity
column with the characteristics ALWAYS GENERATED and NO CYCLE (see “Identity Columns” on page 818).
A primary index cannot contain columns with a Period, Geospatial, JSON, ARRAY, VARRAY,
VARIANT_TYPE, XML, BLOB, CLOB, XML‑based UDT, BLOB‑based UDT, or CLOB‑based UDT data
Distinct and structured UDT columns are valid components of a primary index. UDT columns
based on internal Teradata UDT types (such as Period, ARRAY, VARRAY, and geospatial)
are not valid components of a primary index.
Zero or one primary index must be specified per base table or join index you create
(see “Restrictions on Primary Indexes” on page 262).
The following table types are the only exceptions to the rules that a table or join
index must have a primary index.
Global temporary trace tables.
You cannot define a primary index or any other kind of index on a global temporary
trace table. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language Detailed Topics for details.
Nonpartitioned NoPI tables.
See “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280 for details.
Column‑partitioned tables and join indexes.
See “Column‑Partitioned Tables and Join Indexes” on page 285 for details.
At most, one primary index can be defined per table or join index (see “Restrictions on Primary Indexes” on page 262).
A primary index improves performance when specified correctly in the WHERE clause
of an SQL data manipulation request to perform the following actions (see “Purposes of the Primary Index” on page 262):
Joins between tables with identical primary indexes, the optimal scenario.
Eliminate row partitions when selecting from and joining partitioned tables for various
types of range queries.
System‑derived PARTITION and PARTITION#Ln columns
“PARTITION Columns” on page 801
Primary indexes of temporal tables.
ANSI Temporal Table Support and Temporal Table Support
Row partition elimination
SQL Request and Transaction Processing
Primary indexes and their defaults, restrictions, and uses for:
CREATE HASH INDEX
CREATE JOIN INDEX
SQL Data Definition Language