Exceptions to the PrimaryIndexDefault Setting - Teradata Database

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, PRIMARY AMP INDEX, NO PRIMARY INDEX, PARTITION BY, PRIMARY KEY, and UNIQUE clause. For more information on the PrimaryIndexDefault field, see 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.

You can specify no more than one primary index or primary AMP 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 or a primary AMP index for NoPI tables.

See “NoPI Tables, Column‑Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes” on page 230 and “Column‑Partitioned NoPI Tables and Join Indexes” on page 235 for information about these types of tables.