More and more I read difference between Surrogated Key and PK,
they both turned out to be PK only.. Can any one explain the
difference between the both?
Following is the explanation I have got after my research
(Googling), but if you ask me after the explanation, so what is
the difference than I may not be able to tell you...
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.
they both turned out to be PK only.. Can any one explain the
difference between the both?
Following is the explanation I have got after my research
(Googling), but if you ask me after the explanation, so what is
the difference than I may not be able to tell you...
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.
No comments:
Post a Comment