Relationship of Primary Indexes, Primary Keys, and Foreign Keys - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢

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 Analytics Database 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.

Analytics Database uses primary and foreign keys to maintain referential integrity between tables. Foreign keys are also used in join conditions.