Concept of Key OR Different types of keys in DBMS

A key in DBMS is an attribute or a set of attributes that help to uniquely identify a tuple (or row) in a relation (or table). Keys are also used to establish relationships between the different tables and columns of a relational database. Individual values in a key are called key values.

Why are the Keys Required?

A key is used in the definitions of various kinds of integrity constraints. A table in a database represents a collection of records or events for a particular relation. Now there can be thousands and thousands of such records, some of which may be duplicated.  There should be a way to identify each record separately and uniquely, i.e. no duplicates. Keys allow us to be free from this hassle.

1.Super Key-

1. A super key is a set of attributes that can identify each tuple uniquely in the given relation.
2. A super key is not restricted to have any specific number of attributes.
3. Thus, a super key may consist of any number of attributes.

Example-

Consider the following Student schema-

Student ( roll , name , sex , age , address , class , section )

Given below are the examples of super keys since each set can uniquely identify each student in the Student table-

• ( roll , name , sex , age , address , class , section )
• ( class , section , roll )
• class , section , roll , sex ) ( name , address )

NOTE-

All the attributes in a super key are definitely sufficient to identify each tuple uniquely in the given relation but all of them may not be necessary.

2. Candidate Key-

A minimal super key is called as a candidate key. 

                                OR
A set of minimal attribute(s) that can identify each tuple uniquely in the given relation is called as a candidate key.

Example-

Consider the following Student schema-

Student ( roll , name , sex , age , address , class , section )

Given below are the examples of candidate keys since each set consists of minimal attributes required to identify each student uniquely in the Student table-

• ( class , section , roll ) 
• ( name , address )
  

NOTES-

1. All the attributes in a candidate key are sufficient as well as necessary to identify each tuple uniquely.
2. Removing any attribute from the candidate key fails in identifying each tuple uniquely.
3. The value of candidate key must always be unique.
4. The value of candidate key can never be NULL.
5. It is possible to have multiple candidate keys in a relation.
6. Those attributes which appears in some candidate key are called as prime attributes.
   

3. Primary Key-

A primary key is a candidate key that the database designer selects while designing the database. OR
Candidate key that the database designer implements is called as a primary key.

NOTES-

1. The value of primary key can never be NULL
2.  The value of primary key must always be unique.
3. The values of primary key can never be changed i.e. no updation is possible.
4. The value of primary key must be assigned when inserting a record.
5. A relation is allowed to have only one primary key.

5. Foreign Key-

1. An attribute ‘X’ is called as a foreign key to some other attribute ‘Y’ when its values are dependent on the values of attribute ‘Y’.
2. The attribute ‘X’ can assume only those values which are assumed by the attribute ‘Y’.
3. Here, the relation in which attribute ‘Y’ is present is called as the referenced relation.
4. The relation in which attribute ‘X’ is present is called as the referencing relation.
5. The attribute ‘Y’ might be present in the same table or in some other table.

Here, t_dept can take only those values which are present in dept_no in Department table since only those departments actually exist.

NOTES-

1. Foreign key references the primary key of the table.
2. Foreign key can take only those values which are present in the primary key of the referenced relation.
3. Foreign key may have a name other than that of a primary key.
4. Foreign key can take the NULL value.
5. There is no restriction on a foreign key to be unique.
6. In fact, foreign key is not unique most of the time.
7. Referenced relation may also be called as the master table or primary table.
8. Referencing relation may also be called as the foreign table.