Keys

Keys defined in the relational model

Primary key

A primary key defines each row of the table in a unique manner. Each of its values is unique and not NULL.

Foreign key

A foreign key references an attribute from another table.

Composite key

A composite key is composed of several attributes.

Candidate key

A candidate key is a key with all the properties required to be a primary key.

Alternate key

An alternate key is a candidate key which has not been chosen as the primary key.

Student Table (from the operational system)

A primary key is a set of attributes that functionally determines all the other attributes of the table.

Here, Student_ID and Email are two candidate keys. The combination of Firstname and Lastname cannot be used as a composite key due to the risk of creating duplicates. The key chosen as the primary key is Student_ID.

Keys used in business intelligence

Natural key

A natural key is a key used to define something that belongs to the real world.

Business key

A business key is a primary key from a source system.

Surrogate key

A surrogate key is used as a dimension key; this is a meaningless key that is automatically generated (generally a number sequence) in order to replace a natural key.

Student Dimension (in the data warehouse)

Here, Student_ID is a business and natural key whereas Email is a natural key. To provide insulation from the real world and the source system, an artificial id key is created to ensure that the primary key will not change over time.

Reading Natural key, artificial key: Star Schema. Christopher Adamson. pages 11, 30

Reading Different types of keys: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. page 142