10 minutes read

Types of Keys in the Relational Database Model

Summary:

  • keys in database
  • types of keys in DBMS

DBMS is a complex system and it is often hard to retrieve unique and required data from the database. We need a proper system to identify and retrieve data from the database. For this reason, we have many types of keys in the relational database.

<h1>hiii</h1>
king
<li>vv</li>

What are keys in Relational Database?

Key in DBMS is an attribute or a set of attributes which uniquely identify a row in a table. Keys also allows us to establish a relationship between two or more tables (like Foreign Key).

There are several different types of keys which are dedicated to a specific purpose.

Use and Benefits of Keys in Database

Here are the benefits and uses of keys in DBMS.

  • Uniquely identify each row in the database.
  • Establish a relationship between tables in a database.
  • Maintain integrity and identity in a relationship.

Types of Keys in DBMS

Super Key

A super key is a set of one or more attributes which together uniquely identifies a row in a database.

Take an example of this table which is a part of a sample university database.

ID

NAME

DEPT_NAME

SALARY

122345

Rahul

CS

67500

122346

Saket

CS

68500

122347

Praveen

ME

68500

122348

Rahul

ME

68500

Here in this example the attribute ID can be a super key but NAME cannot be super key alone because two peoples can have the same name.

{NAME, DEPT_NAME} together can be super key in this example because both attributes combined can uniquely identify each row of the database.

{ID, NAME} can also be super key because they uniquely identify each and every tuple of the database.

Candidate Key

The minimal set of the super key which uniquely identifies each row of the database is Candidate key.

In the above example of instructors table, the ID will be candidate key because it alone can uniquely identify each row of the database.

NAME alone cannot be candidate key because it is combined with either ID or DEPT_NAME to uniquely identify each row or tuple of the table.

However, {NAME, DEPT_NAME} together can be candidate key because they uniquely identify each row of the table.

Note: {ID, NAME} cannot be candidate key because ID alone can uniquely identify each row of the database and as per the definition (minimal set of the super key), ID alone is a candidate key.

Primary Key

Database designer chooses a candidate key to be the primary key. It is the main key to determine each and every tuple of the table.

The primary key is unique in all tuples, two tuples in the same relation can NOT have the same primary key.

The fundamental difference between candidates key and primary key is that table can have multiple candidate keys but only one primary key. Candidate key can be NULL but primary key can't be NULL.

Primary keys are denoted by underline in database schema design.

Example.

ID

NAME

DEPT_NAME

SALARY

122345

Rahul

CS

67500

122346

Saket

CS

68500

122347

Praveen

ME

68500

122348

Rahul

ME

68500

Here ID is the primary key because it cannot be NULL  and it uniquely identifies each and every record of the table.

Here are some features of Primary key which are important to remember.

  • Primary Key is unique in all tuples within a table.
  • The primary key cannot be null.
  • Choose an attribute (or set of attributes) as primary key which are less likely to change (Like Aadhaar number of an Indian resident will never change).

Alternate key

A table can have multiple attributes or set of attributes which can uniquely identify each tuple. But we can have only one primary key.

All other keys which uniquely identifies each row of the table but are NOT chosen as the primary key is alternate key.

Example of the alternate key from sample student table:

ID

ROLL_NO

NAME

DEPT_NAME

122345

11111

Rahul

CS

122346

11112

Saket

CS

122347

11113

Praveen

ME

122348

11114

Rahul

ME

In this above example of a sample student database ID is primary key but ROLL_NO can also uniquely identify each row so it is alternate key.

Surrogate Key

A surrogate key or synthetic key do not have any business meaning. They are artificially generated to uniquely identify each and every record in a table.

Example: An auto-increment serial number for a database of books which uniquely identifies it.

We choose to generate a surrogate key when:

  • No attribute has properties to be the primary key
  • The primary key is too big or complicated to deal with

Composite key

Simple Key

Foreign Key

Compound Key

Summary

We have seen all types of keys in DBMS.

Join Our Youtube Channel

Subscribe