15.00 - Relationships Between Secondary Indexes and Primary Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Relationships Between Secondary Indexes and Primary Indexes

You can create a USI on the same column set that defines a non‑unique primary index for a table to enforce uniqueness.

When you do this, the system can use the USI to validate that the primary index columns are unique. You should only use such a USI as a temporary solution for making a NUPI unique. When you alter a table in this way, Teradata Database returns a warning message, and the CREATE TABLE SQL text returned by a SHOW TABLE request is not valid.

You should immediately alter the table using the MODIFY PRIMARY INDEX clause (see “ALTER TABLE (Basic Table Parameters)” on page 31) to change its primary index from a NUPI to a UPI. This action causes Teradata Database to drop the USI that had been defined on the old NUPI because it is no longer needed to enforce the uniqueness of the primary index.

When this redundant USI has been created, you can then use ALTER TABLE to change the primary index on the table from a NUPI to a UPI. You cannot make this conversion for PPIs unless all partitioning columns are also defined in the primary index column list.

You can create a value-ordered NUSI on the same column set that defines the primary index. This is valid for a PPI even if the primary index column set does not include all the columns used in the partitioning expression.

You can create a non‑value‑ordered NUSI on the same column set that defines the primary index for a PPI table when the primary index column set does not include all the columns specified for the partitioning expression.

Defining a USI or NUSI on the same column set as the primary index for a PPI table can provide better performance for primary index accesses to that table.