What do you call an attribute or a combination of attributes that uniquely identifies any given row?

Most of us have come across various articles or documents that explain different types of DBMS keys. It always seems confusing as all keys are related to each other almost similar with slight differences which make it difficult to have a clear understanding. For that reason, I have prepared this article with simple explanations with examples, which also helps while preparing for designing your DBMS system efficiently.

Table of Contents

What are the Keys in DBMS?

Why do we need Keys in DBMS?

Types of Keys in DBMS

1. Super key

2. Candidate key 3. Primary key 4. Alternate key 5. Foreign key 6. Composite key 7. Surrogate key

What is Key in DBMS?

A key in DBMS is an attribute or a set of attributes that help to uniquely identify a row in a table (or in relation to another table). A row is sometimes also referred to as a record or a tuple. An attribute is a column in the table. Keys are also used to establish relationships between the different tables. Many times, keys are used to speed up access to a large database table. A value in the key field of a table is called a key value.

Why do we need Keys in DBMS?

We know that there are countless data in the real world. For storing the data in DBMS, a large number of tables are required. These tables may contain thousands of duplicates, sorted, and unsorted records. Now, to fetch any particular or specific record, without any constraints/ restrictions from these tables is a very difficult process. To overcome all the difficulties, a new concept of Keys arose. Keys ensure that there are no rows with duplicate records in a table.

Types of Keys in DBMS

1. Super Key

2. Candidate Key

3. Primary Key

4. Alternate Key

5. Foreign Key

6. Composite Key

7. Surrogate Key

For our understanding, I am going to consider the following table.

1. Super Key

Super Key is a single attribute or combination of attributes that can be used to uniquely identify a row in a table. A single table can have multiple super keys. A candidate key and primary key can be a super key, but the reverse does not hold true.

Example:

In the above Order Table, we have chosen the Order ID, Customer ID, and Customer Contact ID to uniquely identify tuples. So, the super key set can be as follows:

• [Order ID]

• [Customer ID]

• [Customer Contact ID]

• [Order ID, Customer ID]

• [Customer Contact ID, Order ID]

• [Customer ID, Customer Contact ID]

• [Order ID, Customer ID, Customer Contact ID]

Note: You may decide to use these keys along with other attributes to identify a particular row. Example: [Order ID, Order Date], [Customer ID, Product ID, Product Name], etc. However, in the above example same [Order Date] or [Product ID] and [Product Name] can be used by multiple order entries. So, it doesn’t make sense to use them as Super Keys.

2. Candidate Key

Candidate Keys are those attributes that uniquely identify rows of a table. The primary key of a table is decided based on one of the candidate keys. So, candidate keys have the same properties as the primary keys.

• A single table can have multiple candidate keys.

• Unique values must be present in all columns, chosen as candidate keys.

• They don’t contain any redundant attributes — in our Order Table example, we don’t specify [Order ID, Order Date]as a candidate key.

Example:

[Order ID], [Customer ID], and [Customer Contact ID] can be chosen as the candidate keys from the Order Table.

Real-Life Examples:

• SSN

• PAN No

• Account No

• Passport No

3. Primary Key

The Primary Key is an attribute that can uniquely identify a table. A table can have only one primary key. Out of all the chosen candidate keys, one of the keys should be selected as the primary key.

• Unique values must be present in a column, chosen as a primary key.

• A single table can have only one primary key.

• No NULL value must be present in the column chosen as a primary key.

• A new row cannot be inserted with an existing primary key.

• Every primary key is the candidate key, but a candidate key may or may not be a primary key.

Example:

[Order ID], [Customer ID], and [Customer Contact ID] can be chosen as the candidate keys from the Order Table. Any one of them can be chosen as a Primary key.

4. Alternate Key

All the candidate keys which are not selected as primary keys are known as the Alternate Key.

• Unique values must be present in all columns, chosen as an alternate key.

• The alternate key is a part of the candidate key but is not connected to a primary key.

• In case a table contains a single candidate key then it will be chosen as the primary key. In that case, there won’t be any alternate key.

• Sometimes Secondary/Alternate key is required for the indexing, for better and faster searching.

Example:

If the [Order ID] is selected as the Primary Key, then the [Customer ID]and the [Customer Contact ID] can be used as alternate keys.

5. Foreign Key

Foreign Key is used to establish relationships between two tables.

• The relationship between two tables is known as referential integrity.

• A single table can have multiple foreign keys.

• A foreign key can have NULL values.

• You can have duplicate values in foreign keys.

• The table consisting of the foreign key is known as the CHILD table and the table that is referred to by the foreign key is called the PARENT table.

Example:

[Student_ID] in the Student_Detail Table is referred to the [Student_ID] in the Student_Mark Table.

6. Composite Key

As the name suggests a composite key is a combination of multiple columns that can uniquely identify tuples.

Sometimes single attribute fails to uniquely identify the rows/tuples in a table. However, when taken all together, they ensure uniqueness. The ‘concatenated key’ is another name for a composite key.

Real-Life Examples:

1. Name (Sam Daniel Mccormick)

• First_Name = Sam

• Middle_Name = Daniel

• Last_Name = Mccormick

2. Vehicle License Number Plat (Georgia CDS7854)

• State_ID = Georgia

• License Plat No. = CDS7854

7. Surrogate Key

A Surrogate key is an artificial key that can distinctly identify every row in the table. It is unique, updatable, and can’t be NULL. Surrogate Keys of DBMS are allowed in certain cases when:

• The primary key is too big

• Complicated Primary Key

• Absence of key

Example:

Below Anonymous Table doesn’t have any primary key. Thus, [SR_No.]is used as the Surrogate key. It is generated at the time of the insertion of the records in a table.

Real-Life Examples:

• Phone No.

• Room No.

• Batch No.

I hope you find this blog informative and help to understand the concept of Keys in the DBMS world.

Thank You.

Appreciate your feedback and comment.