DBMS Keys | Primary, Foreign, Super, Candidate, Alternate Key (With Examples)

What are the keys in database?

In database management systems, a key is an attribute or group of attributes that help in identifying any row in tables. They enable you to determine the relationship between two tables. Keys allow you to uniquely identify a row in a table by combining one or more of the table's columns. A database key is also useful for locating a unique record or row in a table.

In the above table, Student ID is a primary key uniquely identifying the student records. No other student can have the same student ID.

In this tutorial, you will learn the following:

What are Keys?
What is the advantage of using keys in a database?
Types of keys in database
Primary key in DBMS
Foreign key in DBMS
Super key in DBMS
Candidate key in DBMS
Alternate key in DBMS
Compound key in DBMS
Composite key in DBMS
Surrogate Key in DBMS

What is the advantage of using keys in a database?

Keys enable you to locate any row of data in a table. A table may include thousands of records in a real-world case where there may be duplication; nevertheless, having keys overcomes this issue. You can construct relationships between tables and enforce identification and integrity in such relationships with its assistance.

Types of keys in database

There are 8 types of keys in DBMS, following are listed below:

Primary Key
Foreign Key
Super Key
Candidate Key
Alternate Key
Compound Key
Composite Key
Surrogate Key

Now, we will learn about these keys with brief examples.

Primary key in DBMS

Primary key in DBMS is a column or group of columns that uniquely identify every row in a table. Since it's unique, it can't be duplicated. A table can't have more than one primary key.

Some rules to note down while defining the primary key:

No two rows can have the same primary key.
Every row must have a primary key.
The primary key field can't be null.
The primary key value can never be modified or updated if any foreign key refers to it.

Example of Primary Key:

In the above example, Student ID is the primary key.

Primary keys are important because they allow you to quickly and easily retrieve specific rows of data from the table.

Foreign key in DBMS

Foreign key in DBMS is a column that creates a relationship between two tables. Foreign keys are used to protect data integrity and facilitate communication between two distinct instances of an entity. As it refers to another table's primary key, it functions as a cross-reference between two tables.

Example of Foreign Key:

The first one is Class Table having ClassCode as the Primary key.

The second one is Teacher Table having TeacherID as the Primary key.

Now, you can see that these tables have no connection between them but with the help of a foreign key, we can create a relationship between these two tables.

In the above table, ClassCode acts as a foreign key, and this concept is called Referential Integrity.

Super Key in DBMS

Super Key in DBMS is a superset of single or multiple keys that identifies rows in a table.

Example of Super Key:

Above is the Employee Table having multiple keys where we can have the following set of possibilities.

{EmpID}
{EmpCode}
{EmpID, EmpCode}
{EmpCode, EmpName}
{EmpID, EmpCode, EmpName}
{EmpCode, EmpName}

Candidate Key in DBMS

Candidate Key in DBMS is a minimal super key with no repeated attributes. The Primary key should be selected from the candidate keys by the database admin. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.

Some properties of the candidate key:

Must contain unique values
The candidate key can't be null
Candidate key in SQL may have multiple attributes

Example of Candidate Key:

From the below table, you can have EmpID and EmpCode as the candidate keys whereas EmpName can have repeated values in the future so it's not considered.

Alternate key in DBMS

A table can have numerous selections for a primary key but only one can be assigned as the primary key. Alternate keys in DBMS are the keys that are not considered primary key.

Example of Alternate Key:

Suppose you have chosen EmpID as the primary key then EmpCode will become the alternate key.

Compound key in DBMS

Compound key in DBMS has two or more attributes that allow you to uniquely recognize a specific record. The compound key in a database is used to uniquely identify each record in the table.

Example of Compound Key:

From the above table, Both OrderNo and ProductID are not unique values so they can't be chosen as the primary key. However, a compound key of OrderNo and Product ID could be used as it will uniquely identify each record.

Composite key in DBMS

Composite key in DBMS is a primary key having two or more columns. Then the question arises what's the difference between a compound key and a composite key. The difference is any part of the compound key can be a foreign key, but the composite key may or maybe not be a part of the foreign key.

Surrogate Key in DBMS

Surrogate Key in DBMS is an artificial key having the sole purpose to be a unique identifier in a table. It has no actual meaning and is just created when you don’t have any natural primary key.

Surrogate keys are permissible when:

The primary key parameter does not exist.
When the primary key in the table is too large or complex.

Example of Surrogate Key:

TimeStamp represents the surrogate key in the above-given example.

You might like:

What Are Slowly Changing Dimensions (SCDs)?

Thanks for reading!

To summarize, We have looked into the eight types of keys in the database. In the next blog, we will learn about normalization concepts.

If you enjoyed reading this blog post and want to show your support, please consider clicking on the link. Every click helps me to continue creating valuable content for my readers. Thank you for your support!

See you next time,

@TechAE