15.00 - Differences Between Keys and Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Differences Between Keys and Indexes

The following table summarizes the differences between keys and indexes using the primary key and primary index for purposes of comparison.

 

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 and column‑partitioned tables and join indexes 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 13: “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.

Required for physical table definition with the following exceptions.

  • NoPI tables
  • Column‑partitioned tables and join indexes
  • These database objects must not have a primary index.