Surrogate Keys
In some design situations the natural key that uniquely identifies a tuple in a relation is difficult to use for software development. For example, it may involve multiple columns or large text fields. 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.
Since primary keys exist primarily as a convenience to the programmer, surrogate primary keys are often used—in many cases exclusively—in database application design.
Due to the popularity of surrogate primary keys, many developers and in some cases even theoreticians have come to regard surrogate primary keys as an inalienable part of the relational data model. This is largely due to a migration of principles from the Object-Oriented Programming model to the relational model, creating the hybrid object-relational model. In the ORM, these additional restrictions are placed on primary keys:
- Primary keys should be immutable, that is, not changed until the record is destroyed.
- Primary keys should be anonymous integer or numeric identifiers.
However, neither of these restrictions is part of the relational model or any SQL standard. Due diligence should be applied when deciding on the immutability of primary key values during database and application design. Some database systems even imply that values in primary key columns cannot be changed using the UPDATE SQL statement.
Read more about this topic: Unique Key
Famous quotes containing the words surrogate and/or keys:
“Detachment is the prerogative of an elite; and as the dandy is the nineteenth century’s surrogate for the aristocrat in matters of culture, so Camp is the modern dandyism. Camp is the answer to the problem: how to be a dandy in the age of mass culture.”
—Susan Sontag (b. 1933)
“McCoy: That shark’s been following us ever since the surgeon died, waiting for the burial. Couldn’t I have a musket to shoot it, sir?
Fletcher Christian: Take the deck, McCoy. I’ll get the keys to the arms chest.
McCoy: Get two muskets, sir. I’d like to shoot that shark on board.”
—Talbot Jennings (1896–1985)