Understanding Candidate Keys and Alternate Keys
In relational database design, candidate keys and alternate keys are essential concepts used to uniquely identify rows and enforce data integrity.
A candidate key is any column or combination of columns that can uniquely identify a row in a table.
A table can have multiple candidate keys.
Among the candidate keys, one is chosen as the primary key.
Candidate keys must be unique and cannot contain NULL values.
In this table, emp_id, email, and phone could all uniquely identify a row. Therefore, all three are candidate keys.
An alternate key is any candidate key that is not selected as the primary key.
It still has the unique property, but it serves as an additional unique constraint.
In the example above, if
emp_idis the primary key, thenemailandphonebecome alternate keys.
Candidate Keys → All possible keys that can uniquely identify a record.
Primary Key → One candidate key chosen as the main identifier.
Alternate Keys → The remaining candidate keys that are not chosen as the primary key.
In summary, every alternate key is a candidate key, but not every candidate key becomes an alternate key.