Computer Science

Key (DBMS)

A key is an attribute or set of attributes that uniquely identifies a tuple in a relation. The keys are defined in relations to access the stored data quickly and efficiently. They are also used to create relationship between different relations.

Types of Keys

Different types of keys used in databases are as follows:

    1. Primary Key
    2. Composite Key / Concatenate Key
    3. Candidate Key
    4. Alternate key
    5. Foreign Key
    6. Secondary key
    7. Sort / Control Key

1. Primary Key

An attributes or set of attributes that uniquely identifies a row or record in a relation is known as primary key. Some most important points about a primary key are as  fellows:

  • 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 Roll No, Name, DOB Address and Phone. The attribute Roll No uniquely identifies each student in the relation. It can be used as primary key for this relation. The attribute Name cannot uniquely identify each row because two students can have same name. It cannot be used as primary key.

Roll No Name DOB Address Phone
1 Ali Raza 05/11/1998 21 Mall Road, Lahore 1234567
2 Asif Naeem 01/01/2000 42 jail Road, Lahore 2642707
3 Hamza Riaz 02/03/2001 12 Tufail Road, Lahore 7234207
The Student relation with primary key as Roll No.

2. Composite Key / Concatenate Key

A primary key that consist of two or more attributes is known as composite key. For example, the following relation uses two attributes Roll No and Subject to identify each tuple. This is an example of composite key.

Roll No Subject Marks
1 English 52
1 Math 77
1 Computer 64
2 English 58
2 Math 69
2 Computer 49
3 English 82
3 Math 98
3 Computer 86
The Marks relation with composite key

3. Candidate Key

A relation may contain many attribute or set of attributes that can be used as primary key. The attribute or set of attributes that can be used as primary key is called candidate key.

Suppose Student relation contains different attributes such Reg No, Roll No, Name, DOB, Address and Phone. The attributes Reg No and Roll No can be used to identify each student in the relation. Both attributes are known as candidate keys. A relation can have many candidate keys.

4. Alternate Key

The candidate keys that are not selected as primary key are known as alternate keys. Suppose Student relation contains different attributes such as Reg No, Roll No, Name, DOB, Address and Phone. The attributes Reg No, and Roll No can be used to identify each student in relation. If Reg No is selected as primary key, then Roll No attribute becomes alternate key.

Reg No Roll No Name DOB Address Phone
UAF-001-2016 1 Ali Raza 05/11/1998 21 Mall Road, Lahore 1234567
UAF-002-2016 2 Asif Naeem 01/01/2000 42 jail Road, Lahore 2642707
UAF-003-2016 3 Hamza Riaz 02/03/2001 12 Tufail Road, Lahore 7234207
Alternate Key

5. Foreign Key

A foreign key is an attribute or set of attributes in a relation whose values match a primary key in another relation. The relation in which foreign key is created is known as dependent relation or child relation. The relation to which the foreign key refers is known as parent relation. The key connects to another relation when a relationship is established between two relations. A relation may contain many foreign keys.

The following figure shows two relations. The Roll No attribute in Parent relation is used as primary key. The Roll No attribute in Child relation is used as foreign key. It refers to Roll No attribute in Parent relation.

Roll No Name Address
1 Ali Faisalabad
2 Ahmad Lahore
3 Nadeem Peshawar
Parent Relation
Roll No Subject Marks
1 English 52
1 Math 77
1 Computer 64
2 English 58
2 Math 69
2 Computer 49
3 English 82
3 Math 98
3 Computer 86
Child Relation

6. Secondary Key

A secondary key is an attribute or combination of attributes that can be used to access or retrieve records. Secondary key values may not be unique. One secondary key value may refer to many records For example, an attribute City in Student relation can be used to display all students who live in a specific city. In this case, City is used as secondary key.

7. Sort / Control Key

An attribute or set of attributes that is used to physically sequence the stored data is called sort key. It is also known as control key. The stored data can be sorted in different ways according to the user requirement.

 

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button