Surrogate (Sequence, GUID) vs Natural key (Business Key)

Data System Architecture


This article talks specifically about the choice of primary key between a


surrogate key are the way to go for a primary key

Quick definition

  • A surrogate key is a key that your system generates. You will find in the form of a Sequence (generated by database) or guid (generated outside the database)
  • A natural key is a key that is not generated by your system such as SSN, VIN, VAT. All business key are natural key

Pros/Cons of using a surrogate key


  • Gain on IO - a surrogate key is way smaller to store than a natural key. The gain will be in:
  • Immutable: As a surrogate is immutable and have no meaning, it will never changes. Therefore, if the unique key of a (external|dimension) table changes, you don't need to modify it


  • You can't search directly on the business key in an foreign/external table.
  • You need to do an extra action to get the primary key (join in SQL or show the information on the screen)

Documentation / Reference

Discover More
Data System Architecture
Relational Data Modeling - Primary key

A primary key is a one or more columns that holds the primary key of a row in a table. A Primary Key identify: the lowest level of a table the unique identifier of a single row doesn't change...

Share this page:
Follow us:
Task Runner