Surrogate Key and Primary Key

A surrogate key is a substitution for the natural primary key. It is a unique identifier or number (normally created by a database sequence generator) for each record of a dimension table that can be used for the primary key to the table.

A surrogate key is useful because natural keys may change.

Difference between a primary key and a surrogate key:

A Primary Key is a special constraint on a column or set of columns. A primary key constraint ensures that the column(s) so designated have no 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. A table may have only one primary key, but it may be composite (consist of more than one column).

A Surrogate Key is any 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 they 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 incremental integer, such as a sequence in Oracle.