In the context of relational databases, an alternate
key (or secondary key) is any candidate
key which is not selected to be the primary key (PK).
For example, a relational database with a table
"employee" could have attributes like "employee_id",
"national_insurance_number", and so on. In this case, both "employee_id"
and "national_insurance_number" serve as unique identifiers for a given
employee, and could thus arguably be used for a primary key. Hence,
both of them are called "candidate keys". If, for example,
"national_insurance_number" was chosen as the primary key, "employee_id"
would become the alternate key.
In a practical RDMS, it is normal to declare any candidate keys /
alternate keys not used as the primary key as 'unique', to prevent
errors where an alternate key ends up with duplicate values due to typos
(this would not happen for the candidate key chosen to be the primary
key, the RDMS would automatically detect it in that case). In the
example above, if "national_insurance_number" was chosen as the primary
key, "employee_id" would be declared unique, and vice versa. In some
practical designs, a designer may use a surrogate
key instead of a natural candidate key - here too the unused
alternate key must be declared as 'unique'.
key (or secondary key) is any candidate
key which is not selected to be the primary key (PK).
For example, a relational database with a table
"employee" could have attributes like "employee_id",
"national_insurance_number", and so on. In this case, both "employee_id"
and "national_insurance_number" serve as unique identifiers for a given
employee, and could thus arguably be used for a primary key. Hence,
both of them are called "candidate keys". If, for example,
"national_insurance_number" was chosen as the primary key, "employee_id"
would become the alternate key.
In a practical RDMS, it is normal to declare any candidate keys /
alternate keys not used as the primary key as 'unique', to prevent
errors where an alternate key ends up with duplicate values due to typos
(this would not happen for the candidate key chosen to be the primary
key, the RDMS would automatically detect it in that case). In the
example above, if "national_insurance_number" was chosen as the primary
key, "employee_id" would be declared unique, and vice versa. In some
practical designs, a designer may use a surrogate
key instead of a natural candidate key - here too the unused
alternate key must be declared as 'unique'.
