Database Keys in DBMS

We must have database keys in DBMS to reduce redundancy in a relational database. Key is a key part of a relational database. Keys ensure that records do not repeat in a database. Keys help in identifying relationship between two tables.

SUPER KEY

Super key is a set of one or more attributes that is able to identifying each record or tuple uniquely. Roll numbers of student is able to identify each student uniquely, and then roll number is a super key of table student. Also combination of name and roll no is able to identify each student uniquely and then combination of name and roll no is super key. But only name of a student is not able to identify each student uniquely because name of two students can be same.

CANDIDATE KEY

Concept of super key is not sufficient because a super key may contain some additional attribute. A Candidate key solves this problem. A candidate key is a sub set of super key. All attributes of super key with no redundant value can be considered as candidate key. A candidate key can be one or more attributes that uniquely identifies each record.

An attribute can be candidate key if it follows following criteria

• It must contain unique value
• It must not contain NULL value
• It must uniquely identify each row or record. 

PRIMARY KEY

A primary is chosen by database designer. A primary key is a candidate key that is most able to uniquely identify each row. In a relation, there may be more than one candidate key. But from that entire candidate keys only most appropriate key is chosen as primary key.  

ALTERNATIVE KEY OR SECONDARY KEY

The attributes that remains after selecting primary key from candidate key can be considered as alternative key. Alternative key is candidate keys – primary key.

FOREIGN KEY

The attributes or columns in a table that points to the primary key of another table is considered as foreign key. Foreign key establishes relationship between two tables.