TechAE Blogs - Explore now for new leading-edge technologies

TechAE Blogs - a global platform designed to promote the latest technologies like artificial intelligence, big data analytics, and blockchain.

Full width home advertisement

Post Page Advertisement [Top]

DBMS Keys

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.

What are the keys in database?

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:

  1. Primary Key
  2. Foreign Key
  3. Super Key
  4. Candidate Key
  5. Alternate Key
  6. Compound Key
  7. Composite Key
  8. 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:

Primary key in DBMS

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:

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

Class Table

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

Teacher Table

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.

Foreign Key example

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:

Super key example

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:

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.

Candidate key example

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:

Alternate key example

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:

Compound key example

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:

Surrogate key example

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

Buy Me A Coffee

No comments:

Post a Comment

Thank you for submitting your comment! We appreciate your feedback and will review it as soon as possible. Please note that all comments are moderated and may take some time to appear on the site. We ask that you please keep your comments respectful and refrain from using offensive language or making personal attacks. Thank you for contributing to the conversation!

Bottom Ad [Post Page]