Natural Keys vs Surrogate Keys: Differences and Similarities


A natural key is a key that comes directly from data in source system (and it is not generated). As it already exists in the real world (case of the social security number, for example), and it is made up of real data, with business value, we may need to rework it when the business requirements change. It is a common value, having a relationship with the rest of the column values to a given data record. 

Additionally, and although it uniquely identifies a single record in a table, when we are dealing with data from multiple source systems, we could have duplicates, depending on what is our natural key: imagine having two different customers, one from source system A and another from source system B, with the same CustomerId.

Another important aspect of natural keys is that they are usually strings, not integers, and because of that they are larger in size.

In contrast, a surrogate key is a database system generated key (typically at run time before the record is inserted into a table) and, consequently, it is an artificial and unnatural value added to the data schema that uniquely identifies a single record in a table.
Without business value, it is not meaningful to users, but it is more efficient for the database system to use, improving query performance, simplifying joins as it is commonly an integer assigned sequentially (easier to store and index in a database). Independent from business changes, it is easier to maintain without the need to be updated.  

Comentários