Primary key
|
|
In database design, a primary key is a value that can be used to identify a particular row in a table. Attributes are associated with it. Examples are names in a telephone book (to look up telephone numbers), words in a dictionary (to look up definitions), and Dewey Decimal Numbers (to look up books in a library).
In the relational model of data, a primary key is a candidate key chosen as the main method of uniquely identifying a tuple in a relation. 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 situations it is impossible to find a natural key that uniquely identifies a tuple in a relation. A surrogate key 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 references 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.