By | September 19, 2019
keys in database

What are Types of keys in database?

Content:

What is Database?

Why we need DBMS

Types of keys in database

Examples of Types of keys in database

Super Key

Minima super key

Candidate key

Primary key

Unique key

Alternate key

Composite key

Foreign key

Natural key

Surrogate key

 

Types of keys in database

Types of keys

 

 

Database Keys:

Introduction

A key is a data item. It is used to identify a record. It can be said that a key is a set of columns that uniquely identifies a record in a table. It fetches or retrieves records from the data table according to the condition.

Key provides several different protocols like columns that cannot store duplicate values. They can also not store null values. Keys are used to generating relationships between the different tables as well. Different types of keys have different constraints and perform differently.

 

A simple definition of keys in Database

A DBMS key is an attribute or a set of attribute that uniquely identifies a row or a record in a relation. Keys help to find the relationship between two tables. Keys can also help to identify a row (tuple) in a relation (table) by a combination of one or more columns in that table.

Example:

Here is a simple example that defines the concept of the key very clearly.

Now employee ID is the primary key in the above table. It is because it is uniquely identifying an employee record. Here, no employee can have the same employee ID.

 

Why we need keys in DBMS:

Here are some reasons why we need keys in the database management system.

  • Keys help in identifying any row in the table.
  • A table could contain thousands of records or even duplicated records, keys assure the uniqueness.
  • They allow establishing a relationship among tables.
  • Keys help in enforcing the identity and integrity in the relationship.

Types of keys in database management system:

The following types of keys in database.

  • Super Key
  • Minima super key
  • Candidate key
  • Primary key
  • Unique key
  • Alternate key
  • Composite key
  • Foreign key
  • Natural key
  • Surrogate key

To better understand we will take two tables. The first table is named “Branch Info” and the other one is called “Student_Information” here.

Branch Info

 

Types of keys in database

 

 

Office Info

 

Types of keys in databases 

 

 

Branch Info

Student_Information

(Note: Word Branch_Id I replaced with Brand_Id)

 

Detail of Types of keys in database

 

Candidate key:

A candidate key is an attribute/ set of attributes that uniquely identifies row or a record in a relation. A table can have more than one candidate keys but it will have only one primary key.

Example:

Possible Candidate keys in Branch_Info table cab be:

  • Brand_Id
  • Brand_Name
  • Brand_Code

Possible Candidate keys in Student_Information table cab be:

  • Student_Id
  • College_Id
  • Rtu_Roll_No

Primary key:

A primary key is used to uniquely identify each record in a table. It can never be the same for two records. It is a set of one or more than one column of a table. The table can have only one primary key. We can select 1 candidate key as a primary key. This key is chosen in a way that its attributes do not change or change rarely.

Rules for defining Primary key

  • Every row must have a primary key.
  • The field of a primary key cannot be null.
  • Two rows can never have the same primary key value.
  • If any foreign key refers to the primary key, the value in primary key column cannot be modified or updated.

Example:

The possible primary key in Brand_Info table are:

  • Brand_Id

The possible primary key in Student_Information table is:

  • College_Id

Types of keys in database

 

Click here for Jobs in Pakistan

 

Alternate key:

A candidate key that is not selected as the primary key is called the alternate key. It can work as the primary key. The alternate key is also said to be the “Secondary key”.

Example:

Possible alternate keys in Brand_Info table are:

  • Brand_Name
  • Brand_Code

Possible Alternate keys in Student_Information table are:

  • Student_Id
  • Rtu_Roll_No

Unique key:

A set of one or more attribute which can be used to uniquely identify the record in a relation is known as a unique key.

It is similar to the primary key but the difference is that a unique key can contain the “Null” value. A primary key cannot have null values. The other difference is that the primary key contains a clustered index but the unique field contains a non-clustered index.

Example:

Possible Unique key in Brand_Info table is:

  • Brand_Name

Possible Unique key in Student_Information table is:

  • Rtu_Roll_No

Composite key

A combination of more than one attribute which can be used to uniquely identify each record in a relation is called a composite key. It is also said to be “Compound key”. A composite key may be a candidate key or a primary key.

Example:

Possible Composite key in Brand_Info table is:

  • {Brand_Name, Brand_Code}

Possible Composite key in Student_Information table is:

  • {Student_Id, Student_Name}

 

Find Jobs in Pakistan: Jang Jobs

 

Super key(Types of keys in database)

A set of one or more than one key that can be used to uniquely identify a record in a table is called the super key. It is a combined form of primary, alternate, and unique keys. These three keys are a subset of a super key.

A super key is a non-minimal candidate key. It means that additional columns not strictly ensure the uniqueness of the tuple. A super key can contain a single column.

Example:

Possible super keys in Brand_Info table are:

  • Brand_Id
  • Brand_Name
  • Brand_Code
  • {Brand_Id, Brand_Code}
  • {Brand_Name, Brand_Code}

Possible super keys in Student_Information table are:

  • Student_Id
  • College_Id
  • Rtu_Roll_No
  • { Student_Id, Student_Name}
  • { College_Id, Brand_Id }
  • { Rtu_Roll_No, Session }

Types of keys in database

Minimal super key(Types of keys in database)

It is a minimum set of columns that can be used to uniquely identify a row. The minimum number of columns which can be joined together to give a unique value for every row in the table.

Example:

Possible Minimal Super Keys in Branch_Info Table are:

  • Brand_Id
  • Brand_Name
  • Brand_Code

Possible Minimal Super Keys in Student_Information Table:

  • Student_Id
  • College_Id
  • Rtu_Roll_No

Natural keys:

It is a key composed of columns that have a logical relationship to other columns within a relation.

Surrogate key:

It is an artificial key that is used to uniquely identify the record in a relation.

Foreign key:

This key is used to generate the relationship between the tables. A foreign key is a field in a database table which is the primary key in some other table. Foreign can contain the null value or duplicate values.

Example:

Brand_Id is playing the role of the foreign key in the Student_Information table. The primary key exists in Brand_Info (Brand_Id) relation. A relation is a word used in place of the table in database terms. In other words, we can say that the table and relation mean the same thing in the database management system.

 

As you read Types of keys in database. See more here:

Types of databases

What are types of registers

2 Replies to “Types of keys in database”

Leave a Reply

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