16.10 - Primary Index Defaults - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

You should always explicitly specify a primary index, a primary AMP index, or explicitly specify no primary index for your tables because the default chosen by the system might not be appropriate.

At the least, you should specify a PRIMARY KEY constraint on the column set you want to define as the primary index if you do not specify an explicit primary index or a primary AMP index.

Teradata Database never creates a partitioned primary index or a primary AMP index by default.

Use the PrimaryIndexDefault field in DBS Control to determine whether Teradata Database will automatically create a primary index for a table created without the PRIMARY INDEX, PRIMARY AMP INDEX, NO PRIMARY INDEX, PARTITION BY, PRIMARY KEY, and UNIQUE clause. For more information on the PrimaryIndexDefault field, see Utilities , B035-1102 Utilities.

No matter how the PrimaryIndexDefault parameter is set, if you do not specify either an explicit PRIMARY INDEX or PRIMARY AMP INDEX clause, an explicit NO PRIMARY INDEX clause, or an explicit PARTITION BY clause, Teradata Database creates the table with a unique primary index only if it is also defined with either a PRIMARY KEY constraint or a UNIQUE column constraint. In this case, an error occurs for a temporal table since it cannot have unique primary index.

Guidelines for NoPI Tables

If you specify an explicit PRIMARY KEY constraint, UNIQUE constraint, or both for an explicitly defined NoPI table, Teradata Database redefines those constraints as USIs, as you can see if you submit a SHOW TABLE request for such a table.

Problematic Selection of an Index Column Set

When the selection of a primary index or primary AMP index column set is problematic, consider either of the following possible solutions.

  • Create it with NO PRIMARY INDEX.

    You can load rows into a NoPI table while you continue to determine an appropriate primary index or primary AMP index. Such a table is sometimes referred to as a sandbox table.

  • Use an identity column as the primary index or primary AMP index for the table (see Surrogate Keys ).