An Alternate Key (or Candidate Key) is a field or set of fields that identify a specific record within a table but is/are not being used as the Primary Key.
This situation occurs very often when using a Surrogate Key. For example, look at the Customer table below:
create table Customer ( ;
CustKey I primary key, ;
CustID c(8) unique, ;
CustName c(30), ;
CustKey must be unique because it is the PK for the table. However, the CustID must also be unique because it is used by the users to locate a specific customer record. This causes the CustID field to be an Alternate Key or Candidate Key. The terms alternate or candidate can be used almost interchangeably.
The difference between an alternate key and a candidate key is simply whether or not you have yet chosen the primary key. Before the primary key is established, the candidates for it are called candidates, while after the selection is made the candidates not selected are called alternate keys.
Contributors: Jim Booth
Category Data Modeling Category Key Fields
( Topic last updated: 1999.07.21 08:52:57 AM )