Surrogate key
From Wikipedia, the free encyclopedia
A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.
Contents |
[edit] Definition
Two definitions of a surrogate appear in the literature:
- Surrogate (1)
- This definition is based on that given by Hall, Owlett and Todd (1976). Here a surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible by the user or application.
- Surrogate (2)
- This definition is based on that given by Wieringa and de Jung (1991). Here a surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application.
The surrogate (1) definition defines its usage in the data model rather than the storage model and is used in this article. See Date (1998).
An important distinction exists between a surrogate and a primary key, depending on whether the database is a current database or a temporal database. A current database stores only currently valid data, therefore there is a one-to-one correspondence between a surrogate in the modelled world and the primary key of some object in the database; in this case the surrogate may be used as a primary key, resulting in the term surrogate key. However, in a temporal database there is a many-to-one relationship between primary keys and the surrogate. Since there may be several objects in the database corresponding to a single surrogate, we cannot use the surrogate as a primary key; another attribute is required, in addition to the surrogate, to uniquely identify each object.
Although Hall et alia (1976) say nothing about this, other authors have argued that a surrogate should have the following constraints:
- the value is unique system-wide, hence never reused;
- the value is system generated;
- the value is not manipulable by the user or application;
- the value contains no semantic meaning;
- the value is not visible to the user or application;
- the value is not composed of several values from different domains.
[edit] Surrogates in practice
In a current database, the surrogate key can be the primary key, generated by the database management system and not derived from any application data in the database. The only significance of the surrogate key is to act as the primary key. It is also possible that the surrogate key exists in addition to the database-generated uuid, e.g. a HR number for each employee besides the UUID of each employee.
A surrogate key is frequently a sequential number (e.g. a Sybase or SQL Server "identity column", a PostgreSQL serial, an Oracle SEQUENCE or a column defined with AUTO_INCREMENT in MySQL) but doesn't have to be. Having the key independent of all other columns insulates the database relationships from changes in data values or database design (making the database more agile) and guarantees uniqueness.
In a temporal database, it is necessary to distinguish between the surrogate key and the primary key. Typically, every row would have both a primary key and a surrogate key. The primary key identifies the unique row in the database, the surrogate key identifies the unique entity in the modelled world; these two keys are not the same. For example, table Staff may contain two rows for "John Smith", one row when he was employed between 1990 and 1999, another row when he was employed between 2001 and 2006. The surrogate key is identical (non-unique) in both rows however the primary key will be unique.
Some database designers use surrogate keys religiously regardless of the suitability of other candidate keys, while others will use a key already present in the data, if there is one.
A surrogate may also be called a
- surrogate key,
- entity identifier,
- system-generated key,
- database sequence number,
- synthetic key,
- technical key, or
- arbitrary unique identifier.
Some of these terms describe the way of generating new surrogate values rather than the nature of the surrogate concept.
Here are some possible candidates for generating surrogates:
- Universally Unique Identifiers (UUIDs)
- Globally Unique Identifiers (GUIDs)
- Object Identifiers (OIDs)
- Sybase or SQL Server identity column
- Oracle
SEQUENCE - PostgreSQL serial
- MySQL
AUTO_INCREMENT - AutoNumber data type in Microsoft Access
[edit] Advantages of Surrogate Keys
[edit] Immutability
Surrogate keys do not change while the row exists. This has two advantages:
- Database applications won't lose their "handle" on the row because the data changes;
- Many database systems do not support cascading updates of keys across foreign keys of related tables. This results in difficulty in modifying the primary key data.
[edit] Flexibility for changing requirements
Because of changing requirements, the attributes that uniquely identify an entity might change. In that case, the attribute(s) initially chosen as the natural key will no longer be a suitable natural key.
- Example:
- An employee ID is chosen as the natural key of an employee DB. Because of a merger with
- another company, new employees from the merged company must be inserted, who have
- conflicting IDs (as their IDs were independently generated when the companies were
- separate).
In these cases, generally a new attribute must be added to the natural key (e.g. an attribute "original_company"). With a surrogate key, only the table that defines the surrogate key must be changed. With natural keys, all tables (and possibly other, related software) that use the natural key will have to change.
More generally, in some problem domains it is simply not clear what might be a suitable natural key. Surrogate keys avoid problems from choosing a natural key that later turns out to be incorrect.
[edit] Performance
Often surrogate keys are composed of a compact data type, such as a four-byte integer. This allows the database to query faster than it could multiple columns.
- A non-redundant distribution of keys causes the resulting b-tree index to be completely balanced.
- If the natural key is a compound key, joining is more expensive as there are multiple columns to compare. Surrogate keys are always contained in a single column.
[edit] Compatibility
Several database application development systems, drivers, and object-relational mapping systems, such as Ruby on Rails or Hibernate (Java), depend on the use of integer or GUID surrogate keys in order to support database-system-agnostic operations and object-to-row mapping.
[edit] Disadvantages of Surrogate Keys
[edit] Disassociation
The values of generated surrogate keys - because they are generated and arbitrary - have no relationship to the real-world meaning of the data held in a row. When inspecting another row holding a foreign key reference to a surrogate key, it is not possible to work out the meaning of it holding that reference simply by looking at the data in the row itself. A layer is added to this indirection for each foreign key join that one must navigate while attempting to make sense of a data item. This can also make auditing more difficult, as incorrect data is not obvious on inspection.
Surrogate keys are also not natural for data that is exported and shared. A particular difficulty is that two instances of a schema can hold records which logically mean the same thing (that is - they are the same in a business sense), but which have a different key owing to the history of how the keys were assigned. An approach to dealing with this is to adopt the rule that surrogate keys are never exported or imported: they are never exposed outside the database except as transient data (most obviously, in executing applications that have a "live" connection to the database).
[edit] Query Optimization
Relational databases assume a unique index is applied to a table's primary key. The unique index serves two purposes: 1) to enforce entity integrity—primary key data must be unique across rows—and 2) to quickly search for rows queried. Since surrogate keys replace a table's identifying attributes—the natural key—and since the identifying attributes are likely to be those queried, then the query optimizer is forced to perform a full table scan when fulfilling likely queries. The remedy to the full table scan is to apply indexes on the identifying attributes, or sets of them. Where such sets are themselves a candidate key, the index can be a unique index.
These additional indexes, however, will take up disk space and slow down inserts and deletes. Choosing what sets of attributes shall be indexed can be something of an art. It depends on what queries are typically run against a database and often on knowledge of the "real world". A database administrator (DBA) will typically monitor the performance of a working database and take note of what queries are frequently run that trigger full table scans, and add indexes accordingly.
[edit] Normalization
The presence of a surrogate key can result in the database administrator forgetting to establish, or accidentally removing, a secondary unique index on the natural key of the table. Without a unique index on the natural key, duplicate rows can appear and once present can be difficult to identify.
Although applications accessing the database should be free of bugs and never attempt to create rows with duplicate natural keys, nevertheless properly maintained unique keys are a vital "last line of defence" against this type of data corruption. This is particularly the case where data is sometimes mutated by user-entered SQL. Of course, due to the very fact that a well-tested and bedded down application will not attempt to introduce duplicate keys, the absence of database constraints is often not immediately apparent as a problem.
[edit] Business Process Modeling
Because surrogate keys are unnatural, flaws can appear when modeling the business requirements. Business requirements, relying on the natural key, then need to be translated to the surrogate key. A strategy is to draw a clear distinction between the logical model (in which surrogate keys do not appear) and the physical implementation of that model, to ensure that the logical model is correct and reasonably well normalised, and to ensure that the physical model is a correct implementation of the logical model.
[edit] Inadvertent Disclosure
Proprietary information may be leaked if sequential key generators are used. By subtracting a previously generated sequential key from a recently generated sequential key, one could learn the number of rows inserted during that time period. This could expose, for example, the number of transactions or new accounts per period. The solution to the inadvertent disclosure problem is to generate a random primary key. However, to prevent duplication which would cause an insert rejection, a randomly generated primary key must ether be queried (to check that it is not already in use), or the key must contain enough entropy that one can be confident that collisions will not happen.
Note that the sequence of keys generated by a psudeo-random number generator seeded with the current system time - even if accurate to the millisecond - does not contain nearly enough entropy to be useful. If, for instance, the generator is known to have been seeded during some particular 24-hour period, then the entire sequence will only have 24 bits (or so) of entropy no matter how many keys it generates. If the generator is only known to have been seeded sometime in the past 10 years, the amount of entropy is still only 38 bits.
[edit] Inadvertent Assumptions
Sequentially generated surrogate keys create the illusion that events with a higher primary key value occurred after events with a lower primary key value. This illusion would appear when an event is missed during the normal data entry process and is, instead, inserted after subsequent events were previously inserted. The solution to the inadvertent assumption problem is to generate a random primary key. However, a randomly generated primary key must be queried before assigned to prevent duplication and cause an insert rejection.
[edit] See also
[edit] References
- Nijssen, G.M. (1976). Modelling in Data Base Management Systems. North-Holland Pub. Co.. ISBN 0-7204-0459-2.
- Engles, R.W.: (1972), A Tutorial on Data-Base Organization, Annual Review in Automatic Programming, Vol.7, Part 1, Pergamon Press, Oxford, pp. 1–64.
- Langefors, B: (1968), Elementary Files and Elementary File Records, Proceedings of File 68, an IFIP/IAG International Seminar on File Organisation, Amsterdam, November, pp. 89–96.
- The identification of objects and roles: Object identifiers revisited by Wieringa and de Jung (1991).
- Relational Database Writings 1994–1997 by C.J. Date (1998), Chapters 11 and 12.
- Carter, Breck. "Intelligent Versus Surrogate Keys". http://www.bcarter.com/intsurr1.htm. Retrieved on 2006-12-03.
- Richardson, Lee. "Create Data Disaster: Avoid Unique Indexes – (Mistake 3 of 10)". http://www.nearinfinity.com/blogs/page/lrichard?entry=create_data_disaster_avoid_unique. Retrieved on 2008-19-01.
- Berkus, Josh. "Database Soup: Primary Keyvil, Part I". http://blogs.ittoolbox.com/database/soup/archives/primary-keyvil-part-i-7327. Retrieved on 2006-12-03.
|
||||||||||||||||||||
This article was originally based on material from the Free On-line Dictionary of Computing, which is licensed under the GFDL.
