Different types of keys in DBMS

Primary 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. 

Foreign key  

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.

Natural 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. 

Surrogate 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 

Durable key 

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.