Skip to content
Related Articles

Related Articles

Improve Article

Surrogate Key in DBMS

  • Last Updated : 10 Jun, 2021

Surrogate key also called a synthetic primary key, is generated  when a new record is inserted into a table automatically by a database that can be declared as the primary key of that table . It is the sequential number outside of the database that is made available to the user and the application or it acts as an object that is present in the database but is not visible to the user or application.

We can say that , in case we do not have a natural primary key in a table, then we need to artificially create one in order to uniquely identify a row in the table , this key is called the surrogate key or synthetic primary key of the table. However , surrogate key is not always the primary key . Suppose we have multiple objects in a database that are connected to the surrogate key, then we will have many-to-one association between the primary keys and the surrogate key and surrogate key cannot be used as the primary key.

Features of the surrogate key :

  • It is automatically generated by the system.
  • It holds anonymous integer.
  • It contains unique value for all records of the table.
  • The value can never be modified by the user or application.
  • Surrogate key is called the factless key as it is added just for our ease of identification of unique values and contains no relevant fact(or information) that is useful for the table.

Consider an example : 
Suppose we have two tables of two different schools having the same column registration_no , name and percentage , each table having its own natural primary key, that is registration_no.
Table of school A – 

registration_nonamepercentage
210101Harry90
210102Maxwell65
210103Lee87
210104Chris76

Table of school B  –



registration_nonamepercentage
CS107Taylor49
CS108Simon86
CS109Sam96
CS110Andy58

Now, suppose we want to merge the details of both the schools in a single table . 
Resulting table will be  –

surr_noregistration_nonamepercentage
1210101Harry90
2210102Maxwell65
3210103Lee87
4210104Chris76
5CS107Taylor49
6CS108Simon86
7CS109Sam96
8CS110Andy58

 As we can observe the above table and see that registration_no cannot be the primary key of the table as it does not match with all the records of the table though it is holding all unique values of the table . Now , in this case, we have to artificially create one primary key for this table. We can do this by adding a column surr_no in the table that contains anonymous integers and has no direct relation with other columns . This additional column of surr_no is the surrogate key of the table.

Advantages of the surrogate key :

  • As there is no direct information related with the table, so the changes are only based on the requirements of the application.
  • Performance is enhanced as the value of the key is relatively smaller.
  • The key value is guaranteed to contain unique information .
  • As it holds smaller constant values , this makes integration of the table easy .
  • Enables us to run fast queries (as compared to the natural primary key)

Disadvantages of the surrogate key :

  • The surrogate key value can never be used as a search key.
  • As the key value has no relation to the data of the table, so third normal form is violated.
  • The extra column for surrogate key will require extra disk space.
  • We will need extra IO when we have to insert or update data of the table.

Some examples of Surrogate key are : 

  • System date & time stamp
  • Random alphanumeric string

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :