6 most important Keys in Database (super key, candidate key,primary key,alternate key, composite and foreign key)

Super key Candidate Key. Primary Key. Alternate Key. Composite Key. Foreign Key.
All types of keys in database

     
 All types of Keys in Database :  

A key is an attribute or set of attributes that uniquely identifies a tuple in a relation. 

The keys are defined in tables to access or sequence the stored quickly and smoothly. 

They are also used to create relationship between different tables.

Types of Keys in Database:

Most important Keys in Database are as fallows:

  1. Super key
  2. Candidate Key.
  3. Primary Key.
  4. Alternate Key.
  5. Composite Key.
  6. Foreign Key.

1) Super Key:

A Super Key is an attribute or combination of attributes in a relation that identifies a tuple uniquely within the relation. A Super key is the most general type of key.

For Example, a relation STUDENT consists of different attribute like Registration No, Name, Father Name, Class and Address. 

The only attribute that can uniquely identifies a tuple in the relation is Registration No. 

The Name attribute cannot identifies a tuple because two or more students may have the same same name. Similarly, Father Name, Class and Address cannot be used to identifies a tuple. 

It means that Registration No is the Super Key for the relation.

Any combination of attributes with the super key is also Super Key. It means any attribute or set of attributes combined with the Super Key Registration No will also become a Super Key.

 A combination of two attribute (Registration NO, Name) is also a Super Key. This combination can also be used to identify a tuple in the relation. Similarly, (Registration No, Class) or (Registration No, Class) are also Super Keys.

2) Candidate Key:

A candidate Key is a Super Key that contains no extra attribute. It consists of minimum possible attributes. A Super Key like (Registration NO, Name) contains an extra field Name. It can be used to identify a tuple uniquely in the relation.

 But it does not consist of minimum possible attribute as only Registration No can be used to identify a tuple in relation. 

It means that  (Registration NO, Name) is a Super Key but it is not a candidate key because it contains and extra field. On the other hand, Registration No is a Super Key as well as a Candidate Key.

3) Primary Key:

A Primary Key is a Candidate Key that is selected by the database designer to identify tuples uniquely in a relation.

A relation may contain many Candidate Keys. When the designer selects one of them to identify a tuple in the relation, it becomes a Primary Key. 

It means that if there is only one Candidate Key, it will be automatically selected as Primary Key.

Some most important points about a primary key are:

  • A relation can have only one Primary Key.
  • Each value in Primary Key attribute must be Unique.
  • Primary Key cannot contain null values.

Suppose a relation Student contains different attributes such as Registration No, Name and Class. The attribute Registration No uniquely identifies each student in the table. 

It can be used as Primary Key for this table. The attribute Name cannot uniquely identify each row because two students can have same name. it cannot be used as Primary Key.

 4) Alternate Key:

The Candidate Key that are not selected as Primary Key are known as Alternate Keys. 

Suppose Student relation contains different attributes such as Registration No, Roll No, Name and Class.

The attribute Registration No and Roll No can be used to identify each student  in the table. If Registration No is selected as Primary Key then Roll No attribute is known as Alternate Key

 

5) Composite Key:

A Primary Key that consists of two or more attributes is known as Composite Key. For Example, the following relation uses two fields Roll No and Subject to identify each tuple.

This is and example of Composite Key.
                       

Roll No
Subject
Marks
1
English
52
1
Maths
77
1
Computer
64
2
English
58
2
Maths
69
2
Computer
49
3
English
82
3
Maths
86
3
Computer
98

 

6) Foreign Key:

A Foreign Key is an attribute or set of attribute in a relation whose values match a Primary key in another relation. 

The relation in which Foreign Key is created is known as dependent table or child table. 

The relation to which the Foreign Key refers is known as Parent table. The Key connects to another relation when a relationship is established between two relations. A relation may contains many Foreign Keys.