Keys in DBMS | Candidate, Super, Primary, and Foreign Key

By Priyanshu Vaish|Updated : May 19th, 2022

This article deals with the keys in DBMS. The keys in DBMS ensure that each record in a table is precisely identified. As you already know, a table represents a singular collection of similar objects or events. Keys are a major component of table-level integrity and relationship-level integrity.

For instance, they enable you to ensure that a table has unique records and that the fields you use to establish a relationship between a pair of tables always contain matching values. Read ahead to learn more about the keys in DBMS.

Table of Content

What are the Keys in DBMS?

The keys in DBMS refer to an attribute/a set of attributes that help us identify a row (or tuple) uniquely in a table (or relation). A key is also used when we want to establish relationships between the different columns and tables of a relational database.

The individual values present in a key are commonly referred to as key values. This article will look at the types of Keys in DBMS according to the Gate Syllabus for CSE. There are various types of keys in DBMS, which are listed below:

  • Candidate Key
  • Super Key  
  • Primary Key 
  • Alternate Key 
  • Foreign Key  
  • Simple Candidate Key 
  • Compound Candidate Key  
  • Overlapping Candidate Key 

Candidate Key in DBMS

The minimum set of attributes that can uniquely identify each and every tuple of the relation. If a relation schema has more than one key, each is called a candidate keys in DBMS.

Example of candidate key:

Consider the relational schema

Student (Sid, Sname, DOB, gender, course, mobile_number)

The examples of candidate keys in DBMS that each set can uniquely identify each student in the Student table are as given below:

  • (Sid, DOB)
  • (Sname, mobile_number)

Super Key in DBMS

The set of attributes that can differentiate records of a relation uniquely. It may not be a minimal attribute set. The candidate keys in DBMS are always the super key, but vice versa is not true.

A super key of the relation schema R= {A1, A2,..., An) is a set of attributes S ⊆ R with the property that no two tuples, t1 and t2, in any legal relation state r of R will have t1[S] = t2[S]. A key K is a super key with the additional property that removing any attribute from K will cause K not to be a super key anymore.
The difference between a key and a super key is that a key has to be minimal; that is, if we have a key K= {A1, A2,..., An) of R, then K-{Aj} is not a key of R for any Aj 1 ≤ I ≤ k.

Example of candidate key:

Consider the relational schema

Student (Sid, Sname, DOB, gender, course, mobile_number)

The examples of super keys in DBMS that each set can uniquely identify each student in the Student table are as given below:

  • (Sid, Sname, DOB, gender, course, mobile_number)
  • (Sid, course, mobile_number)
  • (Sid, Sname, DOB)
  • (Sid, Sname, mobile_number)

Primary Key in DBMS

A primary key is a single attribute or combination of attributes that can uniquely identify a row of data in each table, among various candidate keys in DBMS, at least one candidate key whose field value is not null.

Alternate Key in DBMS

All candidate keys in DBMS of relational schema except primary keys are the alternate keys. The alternate keys are also called secondary keys. Alternate keys(secondary keys) allowed NULL values.

Foreign Key in DBMS

A foreign key is the set of attributes in a table used to refer to the primary key or alternative key of the same or another table. The foreign key allows null values in the column.

Whenever the same type of attribute exists in two different tables, the attribute in one of the tables is declared the primary key. In the other, it is made a foreign key so that the values in both become consistent. A foreign key is dependent on the primary key. Therefore, it is defined over two tables:

Referenced relation: The relation to which other relations refer is called a referenced relation.

Referencing relation: The relation which is referencing another relation is called referencing relation.

An example of the Foreign keys in DBMS is as follows:

byjusexamprep

Simple Candidate Key in DBMS

Only one attribute forms a candidate key. Such a key is called a simple candidate key.

An example of simple candidate keys in DBMS is as follows

Consider the relational schema

Student (Sid, Sname, DOB, gender, course, mobile_number)

The examples of simple candidate keys that each set can uniquely identify each student in the Student table are as given below:

  • (Sid)
  • (mobile_number)
  • (DOB)
  • (Sname)

Compound Candidate Key in DBMS

A candidate key with more than one attribute is called a compound candidate key.

An example of a compound candidate keys in DBMS is as follows:

Consider the relational schema

Student (Sid, Sname, DOB, gender, course, mobile_number)

The examples of compound candidate keys that each set can uniquely identify each student in the Student table are as given below:

  • (Sid, Sname)
  • (Sid, mobile_number)
  • (Sid, DOB)
  • (Sname, mobile_number)

Overlapping Candidate Key in DBMS

The candidate key which has at least one common attribute between multiple candidate keys is known as the overlapping candidate key.

Example: Candidate keys in DBMS: (Sid, Sname), (Sname, SMobile). Here Sname is common to both the keys. So, these candidate keys are called overlapping candidate keys.

Comments

write a comment

FAQs on Keys in DBMS

  • A key refers to an attribute/a set of attributes that help us identify a row (or tuple) uniquely in a table (or relation). A key is also used when we want to establish relationships between the different columns and tables of a relational database.

  • Keys in DBMS play an important role in the relational database. It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables.

  • Alternate keys are those candidate keys that are not the primary key. There can be only one primary key for a table. Therefore all the remaining Candidate keys are known as Alternate or Secondary keys.

  • A primary key is used to ensure data in the specific column is unique. A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It uniquely identifies a record in the relational database table.

  • Both primary and unique keys in DBMS are used to define a row in a table uniquely. Primary Key creates a clustered column index, whereas a unique creates an Unclustered column index. A primary key doesn't allow NULL value. However, a unique Key does allow one NULL value.

Follow us for latest updates