Concept of database keys:

Before understanding the concept of keys we must know what is a table. In a relational database a collection of similar entities can be called a table. Now, what is an entity; anything which has some attributes and those can be used to differentiates one entity from another,  For example  'John' is a student and all information like John's Roll no, class, course are the attributes which make/qualify him as a student. So John is an entity or a tuple or simply a row and Student is entity type or simply the table which holds all student information of a school. Now if we have a collection of students and we need to point a particular student then we need some identifier to identify that student. Here the concept of keys appears in the picture. So a DBMS key is simply an attribute or a set of attributes chosen from the table to be used to uniquely identify an entity(tuple/row). These keys also play a significant role in forming relations between tables.

Different Types of Database Management System Key:
Following are the major DBMS keys:

  • Super Key
  • Candidate Key
  • Primary Key
  • Secondary Key
  • Foreign Key
  • Surrogate Key or Internal Key

Super Key - A super key is a set of attributes that uniquely identifies rows in a table. A Super key may have additional attributes that are not required for unique identification. As the name suggests it is the superset of many keys. In other words Super Key is the superset of candidate/primary key.

Candidate Key -The candidate keys in a table are defined as the set of attributes that is minimal and can uniquely identify any data in the table. i.e a subset of a candidate key cannot uniquely identify a row. Minimality does not mean the minimum number of attributes.

Primary Key -The primary key is selected from one of the candidate keys which suits the business requirement.

Secondary Key -Only one of the candidate keys is selected as the primary key. The rest of them are known as secondary keys.

Foreign Key -A foreign key is an attribute in a table that acts as the candidate/primary key in another. So a foreign key is a set of attributes that references a candidate key.

Surrogate Key or Internal Key- A key that is not supplied by the data source i.e does not have any business or contextual meaning but created internally only to uniquely identify each record, is called a surrogate or internal key. They are created when we do not have any primary key or to improve the performance of the join operation.