Home > Primary key

In the relational model of dataA datum is a statement accepted at face value (a "given"). Data is the plural of datum''. A large class of practically important statements are measurements or observations of a variable. Such statements may comprise numbers, words, or images. Etymology T, a primary key is a candidate keyIn the relational model a candidate key of a relation is a set of attributes of that relation such that (1) in all instances of that relation it holds that there are no two distinct tuples with the same values for these attributes and (2) there is not a p chosen as the main method of uniquely identifying a tuple in a relationIn mathematics a relation is a generalization of arithmetic relations such as " " and "<" which occur in statements such as "5 < 6" or "2 + 2 4". See relation (mathematics), binary relation and relational algebra. A relational database stores data in rela. Practical telephone books, dictionaries and libraries can not use names, words or Dewey Decimal System numbers as candidate keys because they do not uniquely identify telephone numbers, word definitions or books.

In some design situation s it is impossible to find a natural keyIn relational model database design, a natural key is a candidate key that has a logical relationship to the attributes within that row. Data modeling. that uniquely identifies a tuple in a relation. A surrogate keyA surrogate key is a unique primary key generated by the RDBMS that is not derived from any data in the database and whose only significance is to act as the primary key. A surrogate key is frequently a sequential number (e. a Sybase "identity column") bu can be used as the primary key. In other situations there may be more than one candidate key for a relation, and no candidate key is obviously preferred. A surrogate key may be used as the primary key to avoid giving one candidate key artificial primacy over the others.

In addition to the requirement that the primary key be a candidate key, there are several other factors which may make a particular choice of key better than others for a given relation:

• The primary key should generally be short to minimize the amount of data that needs to be stored by other relations that reference it. A compound key is usually not appropriate. (However, this is a design consideration, and some database management systems may be better than others in this regard.)
• The primary key should be immutable, meaning that its value should not be changed during the course of normal operations of the database. (Recall that a primary key is the means of uniquely identifying a tuple, and that identity, by definition, never changes.) This avoids the problem of dangling reference s or orphan records created by other relations referring to a tuple whose primary key has changed. If the primary key is immutable, this can never happen.