17.00 - 17.05 - Relationship of Primary Indexes, Primary Keys, and Foreign Keys - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Advanced SQL Engine
Teradata Database
Release Number
June 2020
English (United States)
Last Update

A primary key defines a column, or columns, that uniquely identify a row in a table. The values defining a primary key for a table:

  • Must be unique
  • Should not change
  • Cannot be null

The following table summarizes the differences between logical primary keys and physical primary indexes:

Primary Key Primary Index
Important element of logical data model. Not used in logical data model.
Used to maintain referential integrity. Used to distribute and retrieve data.
Must be unique to identify each row. Can be unique or nonunique.
Values should not be changed if you want to maintain data integrity and preserve historical relations among tables. Values can change.
Cannot be null. Can be null.
Does not imply access path. Defines the most common access path.
Required only if referential integrity checks are to be performed. Defined for most production tables. (Some staging tables may not have a primary index.)

The database uses a unique primary or secondary index to enforce a primary key; therefore, the primary key can affect how Advanced SQL Engine distributes and retrieves rows.

Although the primary key is often used as the primary index for a table, it may or may not be the best column set to choose as a primary index. You should base your primary index decision on physical database design considerations that may favor columns other than those of the primary key.

A foreign key identifies table relationships. They model the relationship between data values across tables. A foreign key defines a column, or combination of columns, in a table. The foreign key column(s) must exist in the referenced tables as a primary key or a UNIQUE alternate key.

Advanced SQL Engine uses primary and foreign keys to maintain referential integrity between tables. Foreign keys are also used in join conditions.