Database Related

Natural Key In SQL – Definition And Example

Within the context of data warehouses and SQL, you have probably come across the phrase natural key. Actually, a natural key is a key which is made up of columns having a logical relationship within a table with other columns. Here is an example to explain you natural key in simple English.

  • Example of Natural Key

Suppose a table named as People. When we use the columns Address, Last_Name and First_Name together to form a key, then this will be a natural key as those columns are something that are completely natural to people, and there is also a logical relationship between all the columns that may exist in the table.

  • Reason Behind the Name Natural Key

You may want to know that why is it called a natural key. The reason is that the columns that fit into the key are just a part of the table naturally and also have a relationship in the table with other columns. Therefore, a natural key already present within a table– and columns do not require to be added just for the purpose of creating an “artificial” key.

  • Natural keys versus domain keys and business keys

Natural keys are also known as the business keys or domain keys, while all these terms mean exactly the same thing.

  • Surrogate keys versus Natural keys

Generally, Natural keys are compared with surrogate keys. The term surrogate literally means replacement or a substitute. The main reason that why a surrogate key is like a substitute is because it is artificial. This means that there is no logical relationship in between the column used for the surrogate key in the table.

  • Applications of surrogate keys

Since a surrogate is an unnatural key, thus in the most databases, these keys are just used to act as a primary key. These keys are just simple sequential numbers that can be used for uniquely identifying a row. For instance, SQL Server and Sybase both have an identity column specially meant to hold a different sequential number for each row.

Leave a Reply