Good day.

(The article http://habrahabr.ru/en/post/107834/ pushed me to this reflection )

My findings, including:

Autoincre primary key = surrogate key ,

if you have not created a primary key, then the DBMS itself will create a natural key .

Maybe I did not understand what, please correct. Thank you in advance. I do not understand the nature of surrogate and natural keys, trying to figure it out.

  • The surrogate key is an arbitrarily chosen value attributed to a tuple, often consecutive. Natural key is a set of identifiers already in the tuple. For various classifiers (lists, such as postal addresses), the surrogate key is a simple solution to the normalization problem. - maxleo
  • By the way, about the fact that the DBMS itself will create: it will create, you will even see it in the list of indices as heap. In mssql, by the way, the cluster index is responsible for the order of the records, and not some primary key. - maxleo

1 answer 1

A natural key is a column (s) of a table, the uniqueness of the contents of which (which) is a consequence of business rules (for example, the employee's personnel number). However, for the support of uniqueness eats. the primary key of the DBMS is not responsible, but only checks it when inserting and changing the record. The surrogate primary key DBMS itself generates automatically using a sequence. In this case, the primary key will be definitely unique, but will not have any meaningful meaning.

  • Did I understand correctly that the natural key is set by the UNIQUE keyword, and surrogate - for example, INT IDENTITY (1,1)? - Leonard Bertone
  • No wrong: any primary key must be defined as primary. - BuilderC
  • and then how to distinguish between natural and surrogate? - Leonard Bertone
  • one
    Within the meaning of. - maxleo
  • and when creating a new entity, is the key type indicated somewhere? (eats / surrog.) - Leonard Bertone