Different Types of Keys in DBMS

Keys in DBMS:

DBMS Keys are a data item that exclusively identifies a record.

Example: Account_number, Employee_number and customer_number is used as a DBMS Keys because they specifically identify a record stored in a database.

Types of Keys in DBMS:

There are six types of keys in DBMS are:

Different Types of Keys in DBMS

1. Super Key:

A Super key for an entity is a set of one or more attributes whose combined value uniquely identifies the entity in the entity set. Superset of any candidate key is also known as Super key.

Example:
Different Types of Keys in DBMS

2. Primary Key:

The primary key uniquely identifies each record in a table and must never be the same for two records.
Example:

Different Types of Keys in DBMS

3. Candidate Key:

A candidate key is an attribute or a set of attributes that it identifies a record uniquely. These attributes or combinations of attributes are called candidate keys.
Example:

Candidate

Here, Two Candidate Keys are present in this table is given below:
Different Types of Keys in DBMS

4. Composite Key:

A composite key is a group of fields that it combines to uniquely identify a record. When we design a database, we will have tables that will use more than one column as a part of the primary key. So, it is called a composite key or concatenated key.

Example:

Composite table
Here Two Composite Keys are present in this table is given below:

composite

5. Foreign Key:

In relation, the column whose data values correspond to the values of a key column in another relation is called a Foreign Key.

Example:

foreign key
Here, only one Foreign Key is present in both table is given below:

  • {Dept_ID} foreign key

    6. Secondary Key:

    A secondary key is an attribute or combination of attributes that may not be a candidate key but classifies the entity set on a particular characteristic. A table can have multiple choices for a primary key but the only one can be set as the primary key. All the keys which are not the primary key that is called Secondary Key.

    Example:

    secondary key

    In this table, Student_id, Roll_no, email_id are qualified to become a primary key. But since Student_id is only the primary key. Here Two Secondary Keys are present in this table is given below:

  • Roll_no secondary key
  • email_id secondary key