A surrogate key (or synthetic key, pseudokey, entity identifier, factless key, or technical key[citation needed]) 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, unlike a natural (or business) key.[1]
There are at least two definitions of a surrogate:
The Surrogate (1) definition relates to a data model rather than a storage model and is used throughout this article. See Date (1998).
An important distinction between a surrogate and a primary key depends on whether the database is a current database or a temporal database. Since a current database stores only currently valid data, there is a one-to-one correspondence between a surrogate in the modeled world and the primary key of the database. In this case the surrogate may be used as a primary key, resulting in the term surrogate key. In a temporal database, however, 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 al. (1976) say nothing about this, others[specify] have argued that a surrogate should have the following characteristics:
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 (for example, an HR number for each employee other than the UUID of each employee).
A surrogate key is frequently a sequential number (e.g. a Sybase or SQL Server "identity column", a PostgreSQL or Informix serial, an Oracle or SQL Server SEQUENCE or a column defined with AUTO_INCREMENT in MySQL). Some databases provide UUID/GUID as a possible data type for surrogate keys (e.g. PostgreSQL UUID[3] or SQL Server UNIQUEIDENTIFIER[4]).
serial
SEQUENCE
AUTO_INCREMENT
UUID
UNIQUEIDENTIFIER
Having the key independent of all other columns insulates the database relationships from changes in data values or database design[5] (making the database more agile) and guarantees uniqueness.
In a temporal database, it is necessary to distinguish between the surrogate key and the business key. Every row would have both a business key and a surrogate key. The surrogate key identifies one unique row in the database, the business key identifies one unique entity of the modeled world. One table row represents a slice of time holding all the entity's attributes for a defined timespan. Those slices depict the whole lifespan of one business entity. For example, a table EmployeeContracts may hold temporal information to keep track of contracted working hours. The business key for one contract will be identical (non-unique) in both rows however the surrogate key for each row is unique.
Some database designers use surrogate keys systematically regardless of the suitability of other candidate keys, while others will use a key already present in the data, if there is one.
Some of the alternate names ("system-generated key") describe the way of generating new surrogate values rather than the nature of the surrogate concept.
Approaches to generating surrogates include:
IDENTITY
IDENTITY(n,n)
GENERATED AS IDENTITY
INTEGER PRIMARY KEY
AUTOINCREMENT
AS IDENTITY GENERATED BY DEFAULT
Surrogate keys typically do not change while the row exists. This has the following advantages:
Attributes that uniquely identify an entity might change, which might invalidate the suitability of natural keys. Consider the following example:
In these cases, generally a new attribute must be added to the natural key (for example, an original_company column). 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.
Some problem domains do not clearly identify a suitable natural key. Surrogate keys avoid choosing a natural key that might be incorrect.
Surrogate keys tend to be a compact data type, such as a four-byte integer. This allows the database to query the single key column faster than it could multiple columns. Furthermore, a non-redundant distribution of keys causes the resulting b-tree index to be completely balanced. Surrogate keys are also less expensive to join (fewer columns to compare) than compound key.
While using several database application development systems, drivers, and object–relational mapping systems, such as Ruby on Rails or Hibernate, it is much easier to use an integer or GUID surrogate keys for every table instead of natural keys in order to support database-system-agnostic operations and object-to-row mapping.
When every table has a uniform surrogate key, some tasks can be easily automated by writing the code in a table-independent way.
It is possible to design key-values that follow a well-known pattern or structure which can be automatically verified. For instance, the keys that are intended to be used in some column of some table might be designed to "look differently from" those that are intended to be used in another column or table, thereby simplifying the detection of application errors in which the keys have been misplaced. However, this characteristic of the surrogate keys should never be used to drive any of the logic of the applications themselves, as this would violate the principles of database normalization.
The values of generated surrogate keys have no relationship to the real-world meaning of the data held in a row. When inspecting a row holding a foreign key reference to another table using a surrogate key, the meaning of the surrogate key's row cannot be discerned from the key itself. Every foreign key must be joined to see the related data item. If appropriate database constraints have not been set, or data imported from a legacy system where referential integrity was not employed, it is possible to have a foreign-key value that does not correspond to a primary-key value and is therefore invalid. (In this regard, C.J. Date regards the meaninglessness of surrogate keys as an advantage.[9])
To discover such errors, one must perform a query that uses a left outer join between the table with the foreign key and the table with the primary key, showing both key fields in addition to any fields required to distinguish the record; all invalid foreign-key values will have the primary-key column as NULL. The need to perform such a check is so common that Microsoft Access actually provides a "Find Unmatched Query" wizard that generates the appropriate SQL after walking the user through a dialog. (It is, however, not too difficult to compose such queries manually.) "Find Unmatched" queries are typically employed as part of a data cleansing process when inheriting legacy data.
Surrogate keys are unnatural for data that is exported and shared. A particular difficulty is that tables from two otherwise identical schemas (for example, a test schema and a development schema) can hold records that are equivalent in a business sense, but have different keys. This can be mitigated by not exporting surrogate keys, except as transient data (most obviously, in executing applications that have a "live" connection to the database).
When surrogate keys supplant natural keys, then domain specific referential integrity will be compromised. For example, in a customer master table, the same customer may have multiple records under separate customer IDs, even though the natural key (a combination of customer name, date of birth, and e-mail address) would be unique. To prevent compromise, the natural key of the table must not be supplanted: it must be preserved as a unique constraint, which is implemented as a unique index on the combination of natural-key fields.
Relational databases assume a unique index is applied to a table's primary key. The unique index serves two purposes: (i) to enforce entity integrity, since primary key data must be unique across rows and (ii) to quickly search for rows when 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.
Surrogate keys can result in duplicate values in any natural keys. To prevent duplication, one must preserve the role of the natural keys as unique constraints when defining the table using either SQL's CREATE TABLE statement or ALTER TABLE ... ADD CONSTRAINT statement, if the constraints are added as an afterthought.
CREATE TABLE
ALTER TABLE ... ADD CONSTRAINT
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.
Proprietary information can be leaked if surrogate keys are generated sequentially. 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. For example see German tank problem.
There are a few ways to overcome this problem:
Sequentially generated surrogate keys can imply that events with a higher key value occurred after events with a lower value. This is not necessarily true, because such values do not guarantee time sequence as it is possible for inserts to fail and leave gaps which may be filled at a later time. If chronology is important then date and time must be separately recorded.
Penghargaan dan Nominasi Adele Adele performing in 2009 Penghargaan dan Nominasi Penghargaan Menang Nominasi Academy Awards 1 1 American Music Awards 4 5 AIM Independent Music Awards 3 3 Arqiva Commercial Radio Awards 1 1 ARIA Music Awards 0 1 ASCAP Pop Music Awards 1 1 Billboard Music Awards 13 26 BMI London Awards 5 5 BMI Pop Music Awards 4 4 Brit Awards 4 9 BT Digital Music Awards 1 6 CMT Music Awards 0 1 ECHO Music Awards 2 2 European Border Breakers Award 1 1 Fryderyk 2012 Laureaci 1 1 Gold…
物理学 ウィキポータル 物理学執筆依頼・加筆依頼 物理学 Category:物理学 ウィキプロジェクト 物理学 エネルギー保存の法則(エネルギーほぞんのほうそく、英: law of the conservation of energy)とは、「孤立系のエネルギーの総量は変化しない」という物理学における保存則の一つである。エネルギー保存則とも呼ばれる。 概要 任意の異なる二つの状態について、それらの
Pour les articles homonymes, voir Minogue. Cet article est une ébauche concernant une chanteuse et une actrice australienne. Vous pouvez partager vos connaissances en l’améliorant (comment ?) selon les recommandations des projets correspondants. Dannii MinogueDannii Minogue en 2016.BiographieNaissance 20 octobre 1971 (52 ans)Melbourne, Victoria, AustralieNom de naissance Danielle Jane MinoguePseudonymes Dannii, Disco, Club QueenNationalité australienneFormation Lycée Camberwell (…
The ceremonial county of Berkshire currently comprises the unitary authorities of Bracknell Forest, Reading, Slough, West Berkshire, Windsor and Maidenhead and Wokingham. From 1997, it has returned eight MPs to the UK Parliament. As a result of the local government reorganisation introduced by the Local Government Act 1972, which came into effect on 1 April 1974, the boundaries of the historic/administrative county were substantially altered. Northern parts of the county were transferred to Oxfo…
1935 film by Michael Curtiz For the person behind the 17th attempted theft of the Crown Jewels, see Colonel Blood. Captain BloodTheatrical release posterDirected byMichael CurtizScreenplay byCasey RobinsonBased onCaptain Blood1922 novelby Rafael SabatiniProduced by Harry Joe Brown Gordon Hollingshead Starring Errol Flynn Olivia de Havilland Basil Rathbone Ross Alexander Cinematography Ernest Haller Hal Mohr Edited byGeorge AmyMusic byErich Wolfgang KorngoldProductioncompanyCosmopolitan Productio…
No debe confundirse con Música clásica. ClasicismoOrígenes musicales Música galante (1740-1770)Orígenes culturales Clasicismo europeoInstrumentos comunes Piano, violín, clarinete, etc.Popularidad Segunda mitad del siglo XVIIIFusiones Neoclasicismo del siglo XX[editar datos en Wikidata] 1. Luigi Cherubini 2. Carl Philipp Emanuel Bach 3. Joseph Haydn 4. Johann Christoph Friedrich Bach 5. Ludwig van Beethoven 6. Franz Xaver Richter 7. Wolfgang Amadeus Mozart 8. Luigi Bocch…
Fifth series of 2008 British supernatural drama programme Season of television series Being HumanSeries 5Country of originUnited KingdomNo. of episodes6ReleaseOriginal networkBBC ThreeOriginal release3 February (2013-02-03) –10 March 2013 (2013-03-10)Series chronology← PreviousSeries 4 List of episodes Being Human is a British supernatural drama programme created and written by Toby Whithouse for Touchpaper Television. The fifth and final series began airing on BBC Three …
Current Indian prime minister For a chronological guide, see Timeline of the premiership of Narendra Modi. Premiership of Narendra Modi26 May 2014 – presentPrime MinisterNarendra ModiPartyBharatiya Janata Party← Manmohan Singh First term26 May 2014 – 30 May 2019CabinetFirstElection2014Appointed byPresident Pranab MukherjeeSeatVaranasi Second term30 May 2019 – PresentCabinetSecondElection2019Appointed byPresident Ram Nath KovindSeatVaranasi Officia…
Schanajewa beim Damenflorett-Weltcup-Turnier 2014 in Saint-Maur Aida Wladimirowna Schanajewa (russisch Аида Владимировна Шанаева; * 23. April 1986 in Ordschonikidse, Sowjetunion) ist eine russische Florettfechterin, Olympiasiegerin und Weltmeisterin. Erfolge Aida Schanajewa wurde bei den Weltmeisterschaften 2006 in Turin mit der russischen Mannschaft Florett-Weltmeisterin, 2007 in St. Petersburg erhielt sie mit der Mannschaft Silber. Bei den Olympischen Spielen 2008 in Pek…
Cover art by Aileen Miles, 1994 Fianna Tribebook is a supplement published by White Wolf Publishing in 1994 for the horror role-playing game Werewolf: The Apocalypse. Description Fianna Tribebook details the Fianna, one of the tribes of werewolves found in Werewolf: The Apocalypse. The Fianna are a Celtic fey tribe; the book covers information a player would need to know in order to design a character, including tribal history, character templates and new character abilities.[1] This boo…
JaikuJenisOnline Microblogging serviceIndustriOnline ServicesDidirikanFebruari 2006Ditutup15 Januari 2012KantorpusatHelsinki, FinlandSitus webwww.jaiku.com Jaiku adalah suatu situs web layanan jaringan sosial dan mikroblog yang didirikan pada Juli 2006 oleh Jyri Engeström dan Petteri Koponen dengan basis di Helsinki, Finlandia. Namanya diambil dari haiku, suatu bentuk puisi pendek Jepang, yang menurut para pendiri Jaiku menyerupai tulisan-tulisan di Jaiku. Lihat pula Twitter Pownce Pranala luar…
Artikel ini sebatang kara, artinya tidak ada artikel lain yang memiliki pranala balik ke halaman ini.Bantulah menambah pranala ke artikel ini dari artikel yang berhubungan atau coba peralatan pencari pranala.Tag ini diberikan pada Oktober 2022. Amnon dan Tamar, saudara kandung yang melakukan hubungan sedarah. Adelfogami adalah bentuk pasangan seksual eukariota antara sesama saudara, misalnya pada beberapa spesies jamur, tumbuhan berbunga, semut, atau pada manusia. Dalam sosiologi, istilah adelfo…
Este artículo o sección necesita referencias que aparezcan en una publicación acreditada.Este aviso fue puesto el 23 de febrero de 2014. Sylvain Armand Datos personalesNacimiento Saint-Étienne, Francia1 de agosto de 1980 (43 años)Nacionalidad(es) Altura 1,82 metrosCarrera deportivaDeporte FútbolClub profesionalDebut deportivo 1999(Clermont Foot)Posición Lateral izquierdo[editar datos en Wikidata] Sylvain Armand (Saint-Étienne, Francia, 1 de agosto de 1980), es un futbolist…
2014–2015 revolution after the capture of the capital, Sanaa Houthi takeover in YemenPart of the Yemeni Civil War (2014–present)Date21 September 2014 – 6 February 2015(4 months, 2 weeks and 2 days)LocationSanaa, YemenResult Houthi and Saleh loyalists victory leading to a Saudi led intervention. Houthis and Saleh's loyalists take over the Yemeni government Fall of Sanaa to Houthis and Saleh forces Prime Minister Mohammed Basindawa resigns General Ali Mohsen al-Ahmar flees to …
British literary scholar (1889–1963) F. P. Wilson's best known work, English Drama, 1485–1585. Frank Percy Wilson FBA (11 October 1889 – 29 May 1963) was a British literary scholar and bibliographer. Author of many works on Elizabethan drama and general editor of the Oxford History of English Literature, Wilson was Merton Professor of English Literature at the University of Oxford from 1947 to 1957.[1] Education and early career Wilson was born and raised in Birmingham, studyin…
St. Matthew's German Evangelical Lutheran ChurchSt. Matthew's German Evangelical Lutheran ChurchReligionAffiliationEvangelical Lutheran Church in AmericaDistrictSouth Carolina SynodEcclesiastical or organizational statusCongregationLeadershipThe Rev. Eric Childers, Senior Pastor Rev. Rebecca Wicker, Associate Pastor for Outreach and Evangelism Jason Bazzle, Organist and Director of Music LocationLocation405 King Street, Charleston, South Carolina, U.S.A.Geographic coordinates32°47′12″N 79°…
Error that stops the operating system For other uses, see Fatal error. This article needs additional citations for verification. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed.Find sources: Fatal system error – news · newspapers · books · scholar · JSTOR (January 2011) (Learn how and when to remove this template message) Linux 3.8 kernel panic A fatal system error (also known as a…
Queen consort of Bohemia Kunigunda RostislavnaQueen consort of BohemiaTenure1261–1278Coronation1261Born1245?Died9 September 1285 (aged 39–40)PragueBurialPragueSpouseOttokar II of BohemiaZáviš of FalkensteinIssueWenceslaus II of BohemiaKunigunde of BohemiaAgnes, Duchess of AustriaDynastyRurikFatherRostislav MikhailovichMotherAnna of Hungary Kunigunda Rostislavna (1245 – 9 September 1285; Czech: Kunhuta Uherská or Kunhuta Haličská) was Queen consort of Bohemia and its regent from 1278 u…
Overview of the events of 2021 in paleobotany List of years in paleobotany … 2018 2019 2020 2021 2022 2023 2024 … In paleontology 2018 2019 2020 2021 2022 2023 2024 In arthropod paleontology 2018 2019 2020 2021 2022 2023 2024 In paleoentomology 2018 2019 2020 2021 2022 2023 2024 In paleomalacology 2018 2019 2020 2021 2022 2023 2024 In reptile paleontology 2018 2019 2020 2021 2022 2023 2024 In archosaur paleontology 2018 2019 2020 2021 2022 2023 2024 In mammal paleontology 2018 2019 2020 2021…
Nicolai WergelandBorn(1780-11-09)9 November 1780Hosanger, NorwayDied25 March 1848(1848-03-25) (aged 67)Occupation(s)Minister, writer and politicianKnown forMember of the Norwegian Constituent Assembly in 1814Children Camilla Collett Henrik Wergeland Joseph Frantz Oscar Wergeland Nicolai Wergeland (9 November 1780 – 25 March 1848) was a Norwegian minister, writer and politician, and a member of the Norwegian Constituent Assembly at Eidsvoll that wrote the Constitution of Norway on 17 …
Lokasi Pengunjung: 18.118.226.2