Summary -

In this topic, we described about the below sections -

What is Alternate Key?

ALTERNATE KEY is a candidate key which consists of one or more columns used to identify each row uniquely which is not treated as a Primary key. If a table contain many candidate keys among them one is treated as Primary key rest of all are called as Alternate keys.

Properties of Alternate Key

  • Alternate key does not allow duplicate values.
  • Alternate key does not contain NULL values.
  • A table may contain more than one Alternate key.
  • Alternate key may have more than one column.
  • If table consists of only one candidate key. it is treated as Primary key, then there is no Alternate key in that table.
  • All Alternate keys are treated as Candidate keys, but All Candidate keys are not Alternate keys because there is a chance of Primary key.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

In above table, emp_id and emp_name are known as candidate keys. By using those two columns, we can get unique value or row from employee_details table. Among of them, one is treated as PRIMARY KEY and another key known as ALTERNATE KEY.