PRIMARY INDEX - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

The primary index.

The primary index is used by the hashing algorithm to partition table rows across the AMPs.

Queue tables cannot be NoPI tables nor can they have a partitioned primary index.

If no primary index is specified, then Vantage assigns one implicitly, using the following guidelines to determine which column set is to be defined as the primary index.

  • If you do not specify a primary index, but do specify a PRIMARY KEY constraint, then the implicitly defined primary index is the primary key for the table.
  • If you do not specify a primary key, but do specify a UNIQUE constraint, then the implicitly defined primary index is the first UNIQUE constraint defined for the table.
  • If you do not specify either a PRIMARY KEY constraint or a UNIQUE constraint, then the implicitly defined primary index is the QITS column.

    In this case, the primary index is nonunique by default and cannot be defined with a unique constraint or as a USI.

    The reason for this restriction is that timestamp values can repeat and therefore cannot be assumed to be unique.

UNIQUE
The named column must be unique.
The primary index and any secondary indexes can be defined to be unique. The only exception is if a queue table has only a QITS column. In this case, the QITS column must also be the primary index, so it cannot be a UPI. This is because timestamp values can repeat and therefore cannot be assumed to be unique (see Teradata Vantage™ - Data Types and Literals, B035-1143).
primary_index_column
A column in the column set whose values are to be used as the basis for a primary index.
Columns in the list cannot have a BLOB, CLOB, Period, XML, Geospatial, JSON, or DATASET data type.
If you specify more than one column name, the index is created on the combined values of each column named. A maximum of 64 columns can be specified for an index, and a combined maximum of 32 secondary, hash, and join indexes can be created for one table (a multicolumn NUSI defined with an ORDER BY clause counts as two indexes in this calculation). This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints for nontemporal tables and the single-table join indexes used to implement PRIMARY KEY and UNIQUE constraints for temporal tables.