When we talk about Database Management Systems (DBMS), we must talk about keys. Keys play an important role in the relational database. Without them, a database cannot be managed. Keys are not only able to retrieve each record in a table because of their ability to uniquely identify data but also connect different tables together to provide cross-reference. For some keys, they even provide an ability of version control, showing changes in the same subject across time.
This article will show you different types of keys in a database and what they do, and how they interact with data and each other.
Super Key vs Candidate Key
A field or a combination of fields that uniquely identifies a record. Also known as a compound key. Looking at the table below the Super Key are:
- Student ID
- Student ID + Student Name
- Student ID + Student Name + Age
- Student ID + Student Name + Age + Email
- Student ID + Student Name + Age + Email + Phone
- Student ID + Student Name + Age + Email + Phone + Address
- ……. etc (any combination that can uniquely identify a student )
A field or a combination of fields that uniquely and minimally identifies a record. Looking at the table below the Candidate Key are:
- Student ID
- Name + Age
- Name + Address
Primary Key vs Foreign Key
A primary key is just like a candidate key; it also uniquely identifies a record. That is why a primary key is selected from the candidate keys. However, there are still some differences. A table can have multiple candidate keys, like the example shown in the previous section, but it can only have one primary key. Moreover, a candidate key can have a null value but not a primary key. For example, if a student does not have a cell phone and the other does not have an email account, in this case, Email and Phone are still candidate keys; however, they cannot be a primary key, the field that can be the primary key in this table now is the Student ID.
A foreign key is a column or a set of columns in a table whose values correspond to the values of the primary key in another table. It creates a relationship between multiple tables and allows cross-referencing by joining tables together. Since a foreign key doesn’t need to be unique, therefore the common primary key/foreign key relationship is many to one. What is a Many-to-One relationship? Let’s look at the table below.
As you can see, the same product ID serving as a foreign key in the sale table shows more than one time, and the foreign key Product ID in the sale table is equivalent to the Product ID in the product table which would only show once since it serves as a primary key in the product table.
That is because the same customer can order and the same product can be ordered multiple times, so the sales table can show the same customers or product, but it would refer to one single customer or product in the customer table and the product table.
Natural Key vs Surrogate Key vs Durable key
Natural key, as the name suggested, it is a column or a set of columns that already exist in the table and uniquely identifies a record in the table. A natural key is also known as a business key or domain key since it has business meaning and is used in the business world to identify an entity. An entity is an object that has an identity and is independent of the changes in its attributes. It represents long-lived information relevant to the users. For example, a customer, a product, or a transaction. Let’s take the student table again as an example, the ID is the natural key in this table since it is used to identify a student. As long as it is used to identify an entity in a business or a domain, that is a natural key.
Unlike a natural key, a surrogate key is not derived from application data. It is an integer that is generated incrementally by the system and used to identify records uniquely, thus, it has no intrinsic business meaning. A surrogate key is also a common practice when designing a dimensional model. It makes joining tables simpler when a table requires multiple columns to be able to uniquely identify a record. By creating an integer surrogate key for each record and then servers as a foreign key used in other tables
A durable key is similar to a surrogate which is just an integer and does not contain any business meaning. However, not like the surrogate key and natural key which provide a means of identification for data and ensure that data remains unique within an information system. A durable key does not change for different versions of the same entity. It is why a durable key sometimes is referred to as a durable supernatural key. Natural key changes when attributes of an entity change. However, a durable key provides immutability and persistence for the same entity to represent the different versions of an entity across time.
Check out the Lucid Insights blogThere is a variety of content that may help you to improve your business!