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.
What
is the 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 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, such as an auto increment column in
MySQL, or a sequence in Oracle, or an identity column in SQL Server.
"A surrogate key is useful because natural keys may change. "
ReplyDeletePrimary key values cannot change. You can only add or remove primary key values (along with the corresponding record).