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

Relationship of Primary Indexes, Primary Keys, and Foreign Keys

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 by Teradata Database only if referential integrity checks are to be performed.	Defined for most production tables. (Some staging tables may not have a primary index.)

Teradata Database uses a unique primary or secondary index to enforce a primary key; therefore, the primary key can affect how Teradata 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.

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