Next day data processing
Next day feed processing scenarios
September 25, 2017
Repository Queries
Query to get the workflows/sessions run history
October 25, 2017
Show all

What is the difference between Primary Key and Surrogate Key?

Primary Key Vs Surrogate Key

Primary Key Vs Surrogate Key

Primary key:

A primary key is a constraint on a column or set of columns. If EMPLOYEE table is taken for this example, the EMP_ID will be the primary key. We have also said that it can be a set of columns in our first statement, which is for cases where there is a need of combining more than one attribute to make the record unique.

Logically, a primary key constraint ensures that the column(s) does not have any NULL values, and that every value is unique. Physically, a primary key is implemented by the database system using a unique index, and all the columns in the primary key must have been declared NOT NULL. Most of the SQL based GUI tools like Toad, SQL Developer, PL/SQL Developer  and Rapid SQL do that automatically. A table may have only one primary key, but it may be composite (consist of more than one column). Composite key is also termed as Business Key in many of the conversations.

Surrogate key:

A surrogate key is a column or set of columns that can be declared as the primary key instead of a “real” or natural key. Sometimes there can be several natural keys that could be declared as the primary key, and these are all called candidate keys. So a surrogate is a candidate key. A table could actually have more than one surrogate key, although this would be unusual. The most common type of surrogate key is an incrementing integer.

Please refer to this example for clear understanding of what the primary key is and what the surrogate key is.

Here are some other related articles on this topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

Share
+1
Tweet
Pin
Share