In database management systems, an Alternate Key is a candidate key that is not selected as the primary key of a table. When multiple candidate keys exist, one is chosen as the primary key, and the remaining candidate keys become alternate keys.
Alternate keys are still unique and important because they help maintain data integrity and enforce business rules, even though they are not the main identifier of the table.
What is an Alternate Key?
An alternate key is:
A candidate key that uniquely identifies rows
Not chosen as the primary key
Used to enforce uniqueness on other important attributes
Since alternate keys are candidate keys, they must satisfy:
uniqueness
minimality
This means:
no duplicate values are allowed
no unnecessary attributes exist in the key
Example of an Alternate Key
Consider a STUDENT table:
| Roll_No | Name | Branch | CGPA | |
|---|---|---|---|---|
| 101 | aman@email.com | Aman | CSE | 8.5 |
| 102 | riya@email.com | Riya | ECE | 7.9 |
| 103 | kunal@email.com | Kunal | ME | 8.1 |
Suppose:
Roll_Nois selected as the primary keyEmailis also unique and minimal
Then:
Roll_No→ Primary KeyEmail→ Alternate Key
Even though Email is not the primary key, the DBMS still ensures that no two students can have the same email.
SQL Example
Here:
Roll_Nois the primary keyEmailis an alternate key implemented using theUNIQUEconstraint
Characteristics of Alternate Keys
1. Unique
Each alternate key value must uniquely identify a row.
2. Minimal
No attribute can be removed without losing uniqueness.
3. Not the Primary Key
Only one candidate key becomes the primary key; the rest become alternate keys.
4. Business Meaningful
Alternate keys are often real-world identifiers like:
Email
Aadhaar Number
Phone Number
Registration Number
5. Usually Implemented Using UNIQUE Constraint
In SQL, alternate keys are commonly enforced using:
UNIQUE
Candidate Key vs Alternate Key
| Feature | Candidate Key | Alternate Key |
|---|---|---|
| Uniquely identifies rows | Yes | Yes |
| Minimal | Yes | Yes |
| Chosen as primary key | Maybe | No |
| Number possible | Multiple | Remaining candidate keys |
| Purpose | Potential identifier | Secondary unique identifier |
Alternate Key vs Primary Key
| Feature | Primary Key | Alternate Key |
|---|---|---|
| Main identifier | Yes | No |
| NULL allowed | No | Usually No |
| Unique | Yes | Yes |
| Number per table | One | Multiple possible |
| Implementation | PRIMARY KEY | UNIQUE constraint |
Why Alternate Keys Matter?
Alternate keys are important because they:
enforce additional uniqueness constraints
support business rules
prevent duplicate important values
provide alternative search paths
improve indexing and query performance
For example:
two users should not have the same email
two employees should not share the same national ID
These constraints are naturally handled using alternate keys.
Practical Usage of Alternate Keys
Alternate keys are commonly used for:
Email IDs
Aadhaar Numbers
Employee Codes
Username fields
Registration Numbers
Phone Numbers
Even if these are not primary keys, they must remain unique across the table.
Why Not Make Every Candidate Key the Primary Key?
Only one primary key is selected because:
it becomes the main reference for foreign keys
it simplifies schema design
multiple primary keys would create confusion
The remaining candidate keys are still preserved as alternate keys.
Summary
An Alternate Key in DBMS is a candidate key that is not chosen as the primary key of a table. It still uniquely identifies rows and is used to enforce additional uniqueness constraints on important attributes such as email, phone number, or registration number. Alternate keys help maintain data integrity, support business rules, and provide multiple reliable ways to identify records in relational databases.