Primary key

Encyclopedia : P : PR : PRI : Primary key

In database design, a primary key is a value that can be used to identify a unique row in a table. Attributes are associated with it. Examples of primary keys are social security numbers (associated to a specific person) or ISBN numbers (associated to a specific book).

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 and dictionaries cannot use names or words or Dewey Decimal System numbers as candidate keys because they do not uniquely identify telephone numbers or words.

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 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.
Candidate keys have the advantage of uniquely identifying a row without requiring additional storage space. However, it is exceedingly rare that a candidate key remains immutable throughout the life of the database, and changing keys may necessitate significant application reprogramming. As a result, a surrogate key taken from a randomly generated 32-bit integer and validated for redundancy works exceedingly well. They require only four bytes of storage space each, permit just over four billion combinations, and, since they have no meaning in and of themselves, need never be changed.
The primary key should generally be short to minimize the amount of data that needs to be stored by other relations that reference it. If no single domain of the relvar qualifies as a key, a compound key may be appropriate. Physical constraints on the implementation may suggest adding a shorter surrogate key, in order to reduce the redundant storage used. (However, this is a physical design consideration, and some database management systems may be better than others in this regard.)

Primary key

See also