Alternate Key, Candidate key, Composite Key, Foreign Key, Primary Key in RDBMS

We use the database to organize information. To maintain data in a correct and well formed, we use concept of keys. There are five types of keys in database which are as follows –

  • Alternate Key
  • Candidate key
  • Composite Key
  • Foreign Key
  • Primary Key

To understand keys, we will take EmployeeMaster table as example –

EmployeeMaster
{
EmployeeID,
EmpFirstName,
EmpLastName,
DesignationID
}

Table keys are:

Alternate Key:  EmpFirstName + EmpLastName

Candidate keys: EmployeeID or EmpFirstName + EmpLastName

Composite Key:  EmpFirstName + EmpLastName

Foreign Key: DesignationID

Primary Key: EmployeeID

There are five types of keys in database which are as follows –

Alternate Key

  • Any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of  that table
  • For e.g. EmpFirstName + EmpLastName

Candidate key

  • Is not null
  • Have unique records
  • Can create multiple candidate key per table
  • Candidate keys are those keys which is a candidate for primary key of a table
  • For e.g. EmployeeID or EmpFirstName + EmpLastName

Composite Key

  • When we create keys on more than one column then that key is known as composite key
  • For e.g. EmpFirstName + EmpLastName

Foreign Key

  • Can create more than one foreign key per table
  • Foreign key are those keys which is used to define relationship between two tables
  • To implement relationship between two tables
  • Also known as referential integrity
  • For e.g. DesignationID which is reference to DesignationMaster table

Primary Key

  • Is not null
  • Have unique records
  • Only one primary key per table
  • Candidate key which is chosen as a primary key for table is known as primary key
  • For e.g. EmployeeID
Advertisements