Last Updated on April 14, 2023 by Prepbytes
In Dbms (database management system) keys play an important role, A key is a field or combination of fields that uniquely identify a record or row in a table. Keys play a crucial role in maintaining data integrity and consistency within a database. There are several types of keys in DBMS, each with its own unique characteristics and uses. In this article, we will discuss the different types of keys in DBMS along with examples.
Types of Keys in DBMS
Here we have the types of keys in dbms
1) Candidate Key
A candidate key is a set of attributes that can uniquely identify each row in a table. A table can have multiple candidate keys, and we can select one of them as the primary key. Let’s consider an example table named "Students" with the following attributes
| Student ID | First Name | Last Name | Age | Course |
|---|---|---|---|---|
| 1212 | Harsh | Yadav | 23 | IT |
| 1213 | Vikas | Gupta | 22 | Ecs |
| 1214 | Neeraj | Kumar | 22 | CS |
In the "Students" table, we can identify candidate keys such as {StudentID}, {FirstName, LastName}, {StudentID, Course}, etc. For instance, {StudentID} is a candidate key because each student in the table has a unique student ID.
Similarly, {FirstName, LastName} can also be a candidate key because no two students in the table can have the same first name and last name combination. However, this candidate key may not be the best choice for a primary key because some people may have the same first and last name.
Therefore, we can select {StudentID} as the primary key, which can uniquely identify each student in the table. The primary key helps to ensure data consistency and integrity in the table by preventing duplicate or null values.
2) Primary Key
A primary key is a candidate key that is selected as the main key to uniquely identify each row in a table. The primary key helps to ensure data consistency and integrity in the table by preventing duplicate or null values. Let’s consider the same example table named "Students" with the following attributes:
| StudentID | First Name | Last Name | Age | Course |
|---|---|---|---|---|
| 1000 | John | Wick | 17 | Maths |
| 1001 | Joe | Root | 19 | Physics |
| 1002 | Ricky | Ponting | 20 | Computer |
We have identified that {StudentID} is a candidate key in the "Students" table, and we can select it as the primary key to uniquely identify each student in the table. To make {StudentID} a primary key, we can define a primary key constraint on the "StudentID" attribute.
3) Foreign Key
A foreign key is a column or a set of columns in a relational database table that refers to the primary key of another table. It is used to establish a relationship between two tables and ensure data integrity and consistency across them.
In simpler terms, a foreign key is a way to link one table with another table by referencing a primary key in the first table from another table. This ensures that the data entered in one table is valid and corresponds to data in another table.
Example
Customers Table:
| CustomerID | Name Address | City |
|---|---|---|
| Harsh | 123 sector 2 | Delhi |
| Vikas | 12 A sector 3 | Mumbai |
| Naman | 17 A sector 7 | Agra |
Orders Table:
| OrderID | CustomerID | OrderDate | TotalAmount |
|---|---|---|---|
| 1001 | 1 | 2023-01-01 | 100 |
| 1002 | 1 | 2023-03-01 | 200 |
| 1003 | 2 | 2023-02-01 | 300 |
| 1004 | 3 | 2023-04-01 |
In this example, the Customers table has a primary key on the CustomerID field, while the Orders table has a foreign key on the CustomerID field that references the CustomerID field in the Customers table.
The foreign key in the Orders table ensures that only valid CustomerID values are entered in the Orders table. For example, if we try to insert a new order with a CustomerID of 4, which does not exist in the Customers table, we will get a foreign key constraint violation error.
4) Super Key
A super key is a set of one or more attributes that can uniquely identify each row in a table. In other words, a super key is a combination of attributes that can uniquely identify a row, but not necessarily using the minimum number of attributes.
For example, consider a table of employees:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | Kamal | Sales | 5000000 |
| 2 | Ankur | Marketing | 7000000 |
| 3 | Ankit | HR | 6000000 |
| 4 | Hemant | Sales | 9000000 |
In this table, the ID column is the primary key, and it uniquely identifies each row. However, the combination of Name and Department can also uniquely identify each row, making it a super key.
Other examples of super keys could be the combination of ID and Salary, or the combination of Name, Department, and Salary.
It’s worth noting that a super key is not necessarily the most efficient way to identify rows in a table. In many cases, a subset of a super key, known as a candidate key, can be used to uniquely identify each row with the minimum number of attributes.
In general, a super key is useful for identifying relationships between tables and ensuring data consistency and integrity.
5) Alternate Key
An alternate key is a candidate key that is not selected as the primary key for a table. In other words, an alternate key is a set of one or more attributes that can uniquely identify each row in a table, but it is not chosen as the primary key.
For example, consider a table of employees:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | Kamal | Sales | 5000000 |
| 2 | Ankur | Marketing | 7000000 |
| 3 | Ankit | HR | 6000000 |
| 4 | Hemant | Sales | 9000000 |
| 5 | Jagdish | HR | 7500000 |
In this table, the primary key is the ID column, which uniquely identifies each row. However, the combination of Name and Department can also uniquely identify each row, making it an alternate key.
Another example of an alternate key could be a social security number (SSN) for a table of employees or customers. The SSN uniquely identifies each person, but it might not be chosen as the primary key because it is not intuitive or user-friendly.
Alternate keys are important because they can provide additional ways to search and retrieve data from a table. They can also be used in relationships between tables to ensure data consistency and integrity.
6) Composite Key
A composite key is a primary key that consists of more than one attribute or column in a table. In other words, it is a combination of two or more attributes that uniquely identify each row in a table.
Composite keys are useful when a single attribute or column is not sufficient to uniquely identify each row in a table. They are often used in relationships between tables to ensure data consistency and integrity. A composite key can also be used in a foreign key to reference a specific row in another table.
For example, consider a table of orders:
| OrderID | CustomerID | ProductID | OrderDate | Quantity |
|---|---|---|---|---|
| 100 | 101 | 201 | 2023-04-12 | 2 |
| 101 | 102 | 202 | 2023-03-03 | 4 |
| 102 | 101 | 203 | 2023-01-11 | 5 |
In this table, the combination of OrderID, CustomerID, and ProductID can uniquely identify each row, making it a composite key.
Another example of a composite key could be a combination of FirstName, LastName, and BirthDate for a table of people. This combination of attributes could be used to uniquely identify each person.
Composite keys are useful when a single attribute or column is not sufficient to uniquely identify each row in a table. They are commonly used in relationships between tables to ensure data consistency and integrity.
7) Surrogate Key
A surrogate key is a unique identifier that is added to a table in a database to serve as the primary key. It is typically a system-generated value, such as an integer or GUID, that has no meaning to the end user.
For example, consider a table of customers:
| CustomerID | FirstName | LastName | Phone | |
|---|---|---|---|---|
| 1 | Harsh | Yadav | harsh@abc.com | 555-123-4562 |
| 2 | Kamal | Kumar | kamal@abc.com | 535-183-8537 |
| 3 | Lalit | Mehra | lalit@abc.com | 549-193-1565 |
In this table, a surrogate key could be added as the primary key, such as a unique identifier generated by the system In this example, the CustomerID column is a surrogate key, and the original ID values have been replaced with unique system-generated values.
Surrogate keys are useful in situations where there is no natural primary key, or where the natural key is too long, complex, or subject to change. They can also simplify the design of a database by eliminating the need to use multiple columns as a composite key. Additionally, using a surrogate key can make it easier to maintain data integrity and referential integrity between tables.
So these are the types of keys in dbms with examples.
Conclusion
Now we got a clear idea about the types of keys in DBMS and now we can conclude that Keys, play a vital role in maintaining data integrity and consistency within a database. In this article, we have discussed the different types of keys in DBMS including candidate keys, primary key, super key, composition key, alternate key foreign key, and surrogate key
Frequently Asked Question
FAQs on types of keys in dbms
Q1. Why are keys important in DBMS?
Ans. Keys are essential in DBMS because they help to ensure data consistency and integrity. They prevent the creation of duplicate records and help to maintain the relationships between tables. Keys also allow for efficient indexing and searching of data.
Q2. What is a natural key in DBMS?
Ans. A natural key is a column or a set of columns that already exist in the data and can be used to uniquely identify each row in a table. Examples include social security numbers, email addresses, or phone numbers.
Q3. What is a clustered key in DBMS?
Ans. A clustered key is a type of index that determines the physical order of data in a table. It is created based on the primary key or another column that is frequently used for sorting or grouping data.
Q4. How do primary keys work in DBMS?
Ans. A primary key is a column or a set of columns that uniquely identifies each row in a table. It is used to enforce the integrity of data by ensuring that there are no duplicate rows in the table. It also serves as the primary means of identifying each row in the table and is used to establish relationships with other tables through foreign keys.
Q5. How do foreign keys work in DBMS?
Ans. A foreign key is a column or a set of columns in one table that refers to the primary key of another table. It is used to establish a relationship between two tables and enforce referential integrity between them. The foreign key value must match a primary key value in the related table or be null.