Candidate Key Là Gì


Data is growing at an exponential rate & so is the demand for professionals who are well versed with the databases.Bạn đang xem: Candidate key là gì

Organizations all over the world are looking for data scientists and analysts who can draw meaningful insights from the vast amounts of data. Và one of the most important languages for handling databases is SQL. That is why those professionals with a background in SQL have an edge over their peers when it comes khổng lồ working with databases.

Bạn đang xem: Candidate key là gì

Bạn vẫn xem: Candidate key là gì


An important aspect of working with databases is actually creating one. Creating a database is an altogether different ball trò chơi when compared khổng lồ retrieving data from databases. Why so? Well, creating a database requires a thorough knowledge of how the tables relate khổng lồ each other within a database & how to lớn handle records so that there is no duplicate data.

A very important aspect of creating databases is understanding the concept of Keys in SQL. These are nothing but a group of columns that can help you identify rows in a table uniquely. But lượt thích any other entity, there are many kinds of Keys in SQL.

In this article, I will discuss some of the most common SQL keys that any data scientist or analyst should know before they even start working with databases!

I suggest checking out the SQL course for Data Science if you’re new to SQL.

Table of Contents

What are keys in DBMS?Super KeyCandidate KeyPrimary KeyAlternate or Secondary KeyForeign KeyComposite Key

What are keys in DBMS?

Databases are used khổng lồ store massive amounts of information which is stored across multiple tables. Each table might be running into thousands of rows. Needless to lớn say, there will be many duplicate rows with redundant information. How bởi we khuyễn mãi giảm giá with that? How vì chưng we manage records so that we are storing only unique data? And, how bởi we relate the multiple tables that are present in the database?

SQL keys are the answer to lớn all these queries.

An SQL key is either a single column (or attribute) or a group of columns that can uniquely identify rows (or tuples) in a table.

SQL keys ensure that there are no rows with duplicate information. Not only that, but they also help in establishing a relationship between multiple tables in the database. Therefore, it becomes imperative to lớn learn about the different keys in SQL.

What is a Super key in SQL?

Super key is a single key or a group of multiple keys that can uniquely identify tuples in a table.

Super Key can contain multiple attributes that might not be able lớn independently identify tuples in a table, but when grouped with certain keys, they can identify tuples uniquely.

Let me take an example lớn clarify the above statement. Have a look at the following table.


Consider that Id attribute is chất lượng to every employee. In that case, we can say that the Id attribute can uniquely identify the tuples of this table. So, Id is a Super key of this table. Note that we can have other Super Keys too in this table.

For instance – (Id, Name), (Id, Email), (Id, Name, Email), etc. Can all be Super keys as they can all uniquely identify the tuples of the table. This is so because of the presence of the Id attribute which is able lớn uniquely identify the tuples. The other attributes in the keys are unnecessary. Nevertheless, they can still identify tuples.

What is a Candidate key?

Candidate key is a single key or a group of multiple keys that uniquely identify rows in a table.

A Candidate key is a subset of Super keys và is devoid of any unnecessary attributes that are not important for uniquely identifying tuples.

The value for the Candidate key is unique & non-null for all tuples. & every table has khổng lồ have at least one Candidate key. But there can be more than one Candidate Key too.

For example, in the example that we took earlier, both Id and thư điện tử can act as a Candidate for the table as they contain unique & non-null values.


On the other hand, we cannot use the attributes like City or Gender khổng lồ retrieve tuples from the table as they have no unique values.



Primary Key in SQL

Primary key is the Candidate key selected by the database administrator khổng lồ uniquely identify tuples in a table.

Out of all the Candidate keys that can be possible for a table, there can be only one key that will be used to lớn retrieve chất lượng tuples from the table. This Candidate key is called the Primary Key.

There can be only one Primary key for a table. Depending on how the Candidate Key is constructed the primary key can be a single attribute or a group of attributes. But the important point khổng lồ remember is that the Primary key should be a unique & non-null attribute(s).

There can be two ways khổng lồ create a Primary key for the table. The first way is khổng lồ alter an already created to add the Primary key constraint on an attribute. This is shown below:

The second way of adding a Primary key is during the creation of the table itself. All you have to bởi is địa chỉ cửa hàng the Primary Key constraint at the end after defining all the attributes in the table.

To define a Primary Key constraint on multiple attributes, you can menu all the attributes in the parenthesis as shown below.

Alternate or Secondary keys in SQL

Alternate keys are those candidate keys which are not the Primary key.

Xem thêm: Cách Chạm 2 Lần Mở Màn Hình Iphone Và Samsung, Chạm 2 Lần Mở Màn Hình Điện Thoại Iphone, Android

If we look at the Employee table once again, since I have chosen Id as the Primary key, the other Candidate Key (Email), becomes the Alternate key for the table.

Foreign key in SQL

Foreign key is an attribute which is a Primary key in its parent table, but is included as an attribute in another host table.

A Foreign key generates a relationship between the parent table and the host table. For example, in addition to lớn the Employee table containing the personal details of the employees, we might have another table Department containing information related to lớn the department of the employee.

The Primary key in this table is the Department Id. We can showroom this attribute khổng lồ the Employee by making it the Foreign key in the table. We can either vày this when we are creating the table or we can alter the table later to địa chỉ cửa hàng the Foreign Key constraint. Here I have altered the table, but creating Foreign Key during table creation is similar khổng lồ that for Primary Key.

Here, Dep_Id is now the Foreign Key in table Employee while it is a Primary Key in the Department table.

The Foreign key allows you to lớn create a relationship between two tables in the database. Each of these tables describes data related to lớn a particular field (employee and department here). Using the Foreign key we can easily retrieve data from both the tables.

Note: to lớn operate on Foreign keys, you need to know about Joins which you can find out in detail in this article.


Using Foreign keys makes it easier lớn update the database when required. This is so because we only have to make the necessary changes in limited rows. For example, if the sale department shifts from Kolkata khổng lồ Pune, instead of updating it for all the relevant rows in the Employee table, we can simply update the location in the Department table. This ensures that there are only a few places to lớn update và less risk of having different data in different places.

What are Composite keys?

A Composite key is a Candidate key or Primary key that consists of more than one attribute.

Sometimes it is possible that no single attribute will have the property khổng lồ uniquely identify tuples in a table. In such cases, we can use a group of attributes to guarantee uniqueness. Combining these attributes will uniquely identify tuples in the table.

Consider the following table:

Here, neither of the attributes contains chất lượng values to lớn identify the tuples. Therefore, we can combine two or more attributes khổng lồ create a key that can uniquely identify the tuples. For example, we can group Transaction_Id and Product_Id to lớn create a key that can uniquely identify the tuples. These are called composite keys.

Key difference between SQL Keys

SQL keys are used khổng lồ uniquely identify rows in a table.SQL keys can either be a single column or a group of columns.
Super key is a single key or a group of multiple keys that can uniquely identify tuples in a table.Super keys can contain redundant attributes that might not be important for identifying tuples.Candidate keys are a subset of Super keys. They contain only those attributes which are required to lớn uniquely identify tuples.All Candidate keys are Super keys. But the vice-versa is not true.Primary key is a Candidate key chosen to lớn uniquely identify tuples in the table.Primary key values should be unique and non-null.There can be multiple Super keys & Candidate keys in a table, but there can be only one Primary key in a table.Alternate keys are those Candidate keys that were not chosen lớn be the Primary key of the table.Composite key is a Candidate key that consists of more than one attribute.Foreign key is an attribute which is a Primary key in its parent table but is included as an attribute in the host table.Foreign keys may accept non-unique & null values.

Xem thêm: Mạng Chậm Không Vào Được Facebook Chậm, Không Load Được Ảnh Trên


If you are someone who is looking to work with SQL in Python, then I suggest going through this article. Or if you are someone who is looking for some SQL techniques to lớn employ for better data analysis, then you shouldn’t miss this great article.

I hope you enjoyed this article and do connect in the comments if you have any doubts about this topic!