15.00 - Exceptions to the PrimaryIndexDefault Setting - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Exceptions to the PrimaryIndexDefault Setting

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, NO PRIMARY INDEX, PRIMARY KEY, and UNIQUE modifiers. For more information on the PrimaryIndexDefault field, see Utilities: Volume 1 (A-K).

No matter how the PrimaryIndexDefault parameter is set, if you do not specify either an explicit PRIMARY 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.

You can specify no more than one primary index when you create a table with these exceptions:

  • You cannot define a primary, or any other index, for global temporary trace tables regardless of the setting of PrimaryIndexDefault. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language Detailed Topics.
  • You cannot define a primary index for NoPI tables or column-partitioned tables. For column‑partitioned tables, the default primary index specification is NO PRIMARY INDEX regardless of the setting of PrimaryIndexDefault.
  • See “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280 and “Column‑Partitioned Tables and Join Indexes” on page 285 for information about these types of tables.