7 Types of DBMS Keys | Primary, Super, Candidate, Alternate, Foreign, etc.

Summary: You will learn in this article, types of DBMS Keys such as Primary Key, Super Key, Candidate Key, Alternate Key, Foreign Key, Composite Key, Unique Key. The DBMS keys or the Database Management System Keys represent one or more attributes (i.e. it is depending on the types of the DBMS Keys used) from any table in the Database system that brings about to distinctively categorize a row or a combination of more than one column, to recognize or identify the relationship between the tuple (row) in the table, or to determine the relationship between the two tables, which applies to the tables that applies to the tables that are known and queried for analysis or reporting purposes.
DBMS Keys

What are Keys in DBMS?

KEYS in DBMS is a set of attributes or attributes that help you to identify a row(tuple) in a relation. They permit you to find the relation between two tables. Keys help or assist you in uniquely identifying a row in a table by a combination of one or more columns in that table. Furthermore, Key is also helpful for finding unique records or rows from the table. Database key is very helpful for finding unique records or rows from the table.

Example

Student_ID FirstName LastName
101 Roman Orion
102 Cosmo Cillian
103 Lucien Angus
104 Thornton Joaquin
105 Aarav Lorenzo

In the above-given example, you can see that Student_ID is a primary key because it uniquely identifies an Students record. In this table, no other student can have the same Student_ID.

Why We Need a Key?

There are a few reasons for using SQL key in the DBMS system.

  • These keys help you to identify any row of data in a table. during a real-world application, a table could contain thousands of records. Moreover, the records may be duplicated. Keys in RDBMS make sure that you can uniquely identify a table record despite these challenges.
  • It allows you to establish a relationship between and identify the relation between tables
  • These keys also helps us to enforce identity and integrity in the relationship.

Types of Keys in DBMS

There are eight distinct types of Keys in DBMS, each key has different functionality and Role:

S.N DBMS KEYS PROPERTIES
1 Primary Key It is a column or group of columns in a table that uniquely recognizes/identifies every row in that table.
2 Candidate Key Candidate Key is a set of attributes that uniquely identify tuples (Row) in a table. Furthermore, the Candidate Key is a super key with no repeated attributes.
3 Super Key A super key’s a bunch of single or multiple keys that identify rows in a very table.
4 Foreign Key Foreign Key (FK) is a column that creates a relationship between two tables. Furthermore, the main purpose of Foreign keys is to maintain data integrity and allow navigation b/w two different instances of an entity.
5 Alternate Key Alternate Key is a column or group of columns in a table that uniquely recognizes/identifies every row in that table.
6 Composite Key Composite Key is a combination of two or more columns that uniquely recognized/identify rows in a table. Furthermore, the combination of columns guarantees uniqueness, though individual uniqueness isn’t guaranteed.
7 Surrogate Key or Artificial key An artificial key that aims to uniquely identify every record is known as a surrogate key. These kinds of keys are unique as a result they’re created after you don’t have any natural primary keys.
8 Compound Key Compound Key has two or more attributes that allow you to uniquely recognize a specific or particular record. Thus, it’s possible that each column may not be unique by itself within the database.

Let’s look at properties and role each of the keys in DBMS with examples in detail:

1) What is a Primary Key?

A pk-primary key is a column of a table or a set of columns that helps to identify every record present in that table uniquely. Note:- There can be only one primary Key in a table. Furthermore, the primary Key can’t have similar values repeating for any row. Every value of the PK has to be different with no repetitions.

The PRIMARY KEY constraint placed or put on a column or set of columns won’t allow them to have any null values or any duplicates. thus, one table can have only one(1) primary key constraint. Any value in the PK-primary key can’t be modified by any foreign keys which refer to it.

.
Important Note For Primary Key or Rules For Defining Primary key:
Two rows can’t have the same PK (primary key) value
It must for every row have a PK value.
The primary key field can’t be null.
The value in a primary key column can never be changed or updated if any foreign key(FK) refers to that primary key.

What is Primary Key
Primary Key Example: In the given "TEACHERS_DETAILS" table Teacher_ID, is a Primary Key.

2) What is Candidate Key?

CANDIDATE KEY in SQL is a set of attributes that uniquely identify tuples(rows) in a table. Therefore, a candidate Key is a super key with no repeated attributes. The Primary key (PK) should be selected from the candidate keys. each table should have a minimum of one candidate key. A table will have multiple candidate keys but only a single primary key.

.
Important Note For Candidate Key or Properties of Candidate key:
It should contain unique values
Candidate key may have multiple attributes
Must not contain null values
It should contain minimum fields to confirm or ensure uniqueness
Uniquely recognized or identify each record in a table

What is Candidate Key
Candidate key Example: In the given "TEACHERS_DETAILS" table Teacher_Mobile, Teacher_Email, and SSN are candidate keys which help us to uniquely identify or recognized the teachers record in the table.

3) What is Super Key?

A superkey is a group of single or multiple keys that identifies rows in a table. Furthermore, a Super key may have additional attributes that aren’t required for unique identification.

Emp_SSN Emp_Num Name
9956121020 Ep11 Girish
8858224553 EP12 Guillermo
9454364565 Ep13 Ignatius
1169456741 EP14 Joaquin
9856741021 Ep15 Ossian

Super Key Example: In the above-given example, Emp_SSN and Emp_Num are superkeys.

4) What is Foreign key?

The FOREIGN KEY is a column that creates a relationship between two tables. The aim of Foreign keys is to maintain data integrity and allow navigation b/w two different instances of an entity. Furthermore, it acts as a cross-reference between two tables as it references the PK-primary key of another table.
What is Foreign key
Foreign key Example: In this both table TEACHERS_DETAILS and DEPARTMENT, adding with the Department_ID foreign key to establised the connection between the tables. we can create a relationship between the two tables.

5) What is Alternate key?

Alternate Key is a column or group of columns in a table that uniquely recognizes/identifies every row in that table.

In another word, we can say that there may be one or more attributes or a combination of attributes that uniquely identify each tuple (rows) in a relation. These attributes or combinations of the attributes are known as the candidate keys. One key is chosen as the PK-primary key from these candidate keys, and the remaining candidate key, if it exists, is termed the alternate key.

In another way, the total number of the alternate keys is the total number of candidate keys minus the primary key (PK). The alternate key in SQL may or may not exist. If there is only one(1) candidate key in a relation, it doesn’t have an alternate key.

Alternate key Example: TEACHERS_DETAILS relation has two attributes, Teacher_ID and PAN_Number, that act as candidate keys. In this relation, Teacher_ID is chosen as the primary key, so the other candidate key, PAN_Number, acts as the Alternate key.
What is Alternate key: dbms keys

6) What is Composite key?

COMPOSITE KEY basically is a combination of two or more columns that uniquely identify rows in a table. Furthermore, the combination of columns guarantees uniqueness, though individually uniqueness isn’t guaranteed. Hence, they are combined to uniquely recognize or identify records in a table.

The difference between the compound key and the composite key is that any part of the compound key can be an FK (foreign key), but the composite key may or maybe not be a part of the FK-foreign key.
Composite key Example: In TEACHERS_DETAILS relations, we assume that a Teacher may be assigned multiple roles and a Teacher teaching on multiple subjects simultaneously. So the primary key will be composed of all three attributes, namely Teacher_ID, Teacher_Role, and Subject_ID in combination.
So these attributes act as a composite key since the PK-primary key comprises more than one attribute.
What is Composite key: dbms keys

7) Surrogate Key or Artificial key

SURROGATE KEYS is an artificial key that purpose to uniquely identify each record is known as a surrogate key. This type of partial key in DBMS is unique because it’s created when you don’t have any natural primary key (PK). They don’t lend any meaning to the data in the table. Furthermore, the surrogate key in SQL is usually an integer. Thus, a surrogate key is a value generated right before the record is inserted into a table.

In another word, we can say that The key created using arbitrarily assigned data are called artificial keys. These keys are created when a PK-primary key is large and complex and has no relationship with many other or several relations. Furthermore, the data values of the artificial keys are usually numbered in a serial order.
Surrogate Key or Artificial key Example: The primary key (PK), which is composed of Teacher_ID, Teacher_Role, and Subject_ID, is large in teachers relations. So it would be better to add a new virtual attribute to recognize or identify each tuple(rows) in the relation uniquely.
What is Artificial key: dbms keys

8) What is Compound Key?

COMPOUND KEY in DBMS has two or more attributes that permit you to uniquely recognize a specific or particular record. Furthermore, it’s possible that each column may not be unique by itself within the database. but, when combined with the other column or columns the combination of composite keys become unique. The aim of the compound key in the database is to uniquely recognize or identify each record in the table.
Compound Key Example:

Order_ID Order_Number Porduct_ID Porduct_Quantity Product_Name
11 AD001 ABC4714 13 Trackball
12 AD001 BCZ1245 25 Pointing stick
13 AD006 ABC4754 56 Monitor
14 AD004 XYZ1231 36 Mouse
15 AD006 ABC4714 76 Light pen

In this example, we can see that Order_Number and Product_ID can’t be a primary key (PK) as it doesn’t uniquely recogised or identify a record. However, a compound key of Order_ID could be used as it uniquely identified each record.

What is The Difference Between Primary key And Foreign key

Primary Key Foreign Key
The primary key Helps us to uniquely identify a record in the table. It is a field in the table that is the primary key (PK) of another table.
Primary Key can not accept null values. A foreign key (FK) may accept multiple null values.
The PK-primary key is a clustered index and records in the DBMS table are physically organized in the sequence of the clustered index. An FK can’t automatically create an index, clustered or non-clustered. but, you can manually create an index on the FK-foreign key.
You can have the single Primary key (PK) in a table. You can have multiple FK on a table.

Conclusion:

All Keys and functional dependencies play an important role in designing a database. These concepts also facilitate finding the difference between good and bad database design. The final process or method of removing redundancies and making the database efficient is normalization, which uses all concepts mentioned in this DBMS keys article.

  • Keys in RDBMS allow us to establish a relationship between & identify the relation between tables
  • There are eight types of keys in DBMS are Primary, Foreign, Super, Candidate, Alternate, Compound, Composite, and Surrogate Key.
  • A super key with no repeated attribute is known as a candidate key
  • A compound key is a key that has many fields which allow you to uniquely identify a specific record
  • A key that has multiple attributes to uniquely identify rows in a table is known as a composite key
  • An artificial key that aims to uniquely identify/recognised each record is called a surrogate key
  • Primary Key(PK) never accepts null values while a foreign key(FK) may accept multiple null values.
  • A super key is a group of single or multiple keys which allow us to recognised or identifies rows in a table.
  • A column or group of columns in a table that helps us to uniquely identifies or recognized every row in that table is known as a primary key
  • All the different keys in DBMS which aren’t primary keys (PK) are called an alternate key