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:
-
- Primary Key
- Composite Key / Concatenate Key
- Candidate Key
- Alternate key
- Foreign Key
- Secondary key
- 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 |
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 |
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 |
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 |
| 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 |
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.
