Differences Between Keys and Indexes - Teradata Database

Primary Key

Primary Index

Component of logical data model.

Primary keys are also used to declare and maintain referential integrity constraints in the physical database, as noted by the next row in the table. A primary key is implemented as a unique primary or secondary index in the physical Teradata Database.

Component of physical data model.

Used to maintain referential integrity.

Used to distribute and retrieve rows.

Must be unique.

Can be nonunique.

Uniquely identifies each row.

Distributes table rows across the AMPs.

Might or might not uniquely identify each row depending on whether it is defined as a UPI or a NUPI.

The rows of NoPI tables are distributed across the AMPs differently because they do not have a primary index.

Values can never be changed.

Values can be changed, but as a general rule should not be.

Cannot be wholly or partly null.

Can be wholly or partly null.

A single‑column primary index can apply to no more than one row per table.

A multicolumn primary index can have a row for any combination of nulls and values, but cannot have the same combination for more than one row.

Although you can have a wholly or partly null primary index, there are countless reasons why you should not. See Chapter 12: “Designing for Missing Information” for a discussion of why nulls should be avoided when possible in relational databases.

Does not imply an access path.

This does not mean that the primary key for a table is never used as an access or join path by the Optimizer.

Defines row distribution to the AMPs and an access path.

Not required for physical table definition

Not required. A table or join index can have a primary AMP index or no primary index. An object with a primary AMP index must be column partitioned, and a NoPI join index must be column partitioned.