Surrogate Key

An additional column added into a table to uniquely identify each row having non business value

  • Its values are automatically generated at the time of record insertion
  • Its values are not known to end users i.e. these are not available in end user reports
  • It has full potential to become a primary key into a table due to its unique data
  • It is useful when your records have no natural key ( such as Person table)

MS SQL Server uses IDENTITY to generate surrogate key data. When used a primary key, it is liable to use this key as foreign key to make relationship between table.

Pros :

  • Business data is independent from this key. Hence, any change in other keys (business columns) would not lead to change primary and foreign key relationships. Because, primary and foreign keys are surrogate values and have no business value.
  • Smaller in size ( Integer, most of the time). Hence, smaller index.
  • Easy to guarantee uniqueness.

Cons:

  • Not useful while searching data (No business value)
  • Separate indexes are required on natural data columns when surrogate key is used a primary key.
Surrogate Key