What is an alternative (alternate) key?

An alternate key is any attribute or set of attributes in a database table that can serve as a unique identifier for each record but is not the primary key.

It has the following properties:

• Uniqueness: Each value or combination of values of the alternate key is unique for each row in the table.
• No NULLs: Usually, attributes that are part of the alternate key cannot contain NULL values (although in some DBMSs, this may be optional).
• Can become a primary key: Any alternate key can be chosen as the primary key of the table.

Examples:

• In the Customers table, the attributes customer_id (primary key) and email (alternate key) can serve as unique identifiers.
• In the Products table, the attributes product_id (primary key) and the combination of manufacturer and model_number can be alternate keys.

Difference from the primary key:

• The primary key is the single chosen unique identifier for the table.
• Alternate keys are all other unique identifiers that could be the primary key but were not chosen as such.

Alternate keys are important for maintaining data integrity and can be used for searching or linking between tables, even if they are not primary keys.