Provide an example and definition of a natural key in SQL.
You have probably come across the term natural key within the context of SQL and data warehouses. What exactly is a natural key? A natural key is a key composed of columns that actually have a logical relationship to other columns within a table. What does that mean in plain English? Well, let’s go through an example of a natural key.
Natural Key Example
Consider a table called People. If we use the columns First_Name, Last_Name, and Address together to form a key then that would be a natural key because those columns are something that are natural to people, and there is definitely a logical relationship between those columns and any other columns that may exist in the table.
Why is it called a natural key?
The reason it’s called a natural key is because the columns that belong to the key are just naturally a part of the table and have a relationship with other columns in the table. So, a natural key already exists within a table – and columns do not need to be added just to create an “artificial” key.
Natural keys versus business keys
Natural keys are often also called business keys – so both terms mean exactly the same thing.
Natural keys versus domain keys
Domain keys also mean the same thing as natural keys.
Natural keys versus surrogate keys
Natural keys are often compared to surrogate keys. What exactly is a surrogate key? Well, first consider the fact that the word surrogate literally means substitute. The reason a surrogate key is like a substitute is because it’s unnatural, in the sense that the column used for the surrogate key has no logical relationship to other columns in the table.
In other words, the surrogate key really has no business meaning – i.e., the data stored in a surrogate key has no intrinsic meaning to it.
Why are surrogate keys used?
A surrogate key could be considered to be the “artificial” key that we mentioned earlier. In most databases, surrogate keys are only used to act as a primary key. Surrogate keys are usually just simple sequential numbers – where each number uniquely identifies a row. For example, Sybase and SQL Server both have what’s called an identity column specifically meant to hold a unique sequential number for each row. MySQL allows you to define a column with the AUTO_INCREMENT attribute, which just means that the value in the column will automatically increment the value in a given column to be 1 greater than the value in the previous row. This just means that every time you add a new row, the value in the column that is auto incremented is 1 greater than the value in the most recent row added to the table. You can also set the increment value to be whatever you want it to be.