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.
Advertisements
Surrogate Key

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s