Surrogatnøkkel

En surrogatnøkkel i en database er en unik identifikator for enten en entitet i den modellerte verdenen eller et objekt i databasen. Surrogatnøkkelen er ikke utledet fra applikasjonsdata, i motsetning til en naturlig nøkkel.

Definisjon

Det er minst to definisjoner på en surrogat:

Surrogat (1) – Hall, Owlett og Todd (1976)

Surrogatet representerer en entitet i omverdenen. Den er internt generert av systemet, men er likevel synlig for brukeren eller applikasjonen.

Surrogat (2) – Wieringa og De Jonge (1991)

Surrogatet representerer et objekt i selve databasen. Den er internt generert av systemet, og er usynlig for brukeren eller applikasjonen.

Den første definisjonen er relatert til datamodeller (Date, 1998), og brukes gjennom denne artikkelen. Den andre definisjonen er relatert til lagringsmodeller.

Et viktig skille mellom en surrogat- og primærnøkkel avhenger av om databasen er en gjeldende database eller en temporal database:

• Siden en gjeldende database bare lagrer data som er gyldig for øyeblikket er det en en-til-en-korrespondanse mellom et surrogat i den modellerte verden og primærnøkkelen til databasen. I dette tilfellet kan surrogaten brukes som en primærnøkkel, hvilket resulterer i begrepet surrogatnøkkel.
• I en temporal database er det imidlertid en mange-til-en-relasjon mellom primærnøkler og surrogaten. Siden det kan være flere objekter i databasen som tilsvarer et enkelt surrogat kan vi ikke bruke surrogatet som primærnøkkel. Med andre ord er det nødvendig med et annet attributt i tillegg til surrogatet for å identifisere hvert objekt unikt.

Selv om Hall med kolleger (1976) sier ingenting om dette, har andre hevdet at et surrogat bør ha følgende egenskaper:

• Verdien blir aldri gjenbrukt
• Verdien er systemgenerert
• Verdien kan ikke manipuleres av brukeren eller applikasjonen
• Verdien inneholder ingen semantisk betydning
• Verdien er ikke synlig for brukeren eller applikasjonen
• Verdien er ikke sammensatt av flere verdier fra forskjellige domener

Surrogater i praksis

I en gjeldende database kan surrogatnøkkelen være primærnøkkelen generert av databasehåndteringssystemet, og ikke utledet fra applikasjonsdata i databasen. Den eneste betydningen surrogatnøkkelen har er å fungere som primærnøkkel. Det er også mulig at surrogatnøkkelen eksisterer i tillegg til den databasegenererte UUID-en (for eksempel et HR-personalnummer for hver ansatt som er annerledes enn UUID-en til hver ansatt, f.eks. ansattnummer).

En surrogatnøkkel er ofte et løpenummer (for eksempel "identity column" i Sybase og SQL Server, en serial i PostgreSQL eller Informix, en SEQUENCE i Oracle eller SQL Server, eller en kolonne i MySQLdefinert med AUTO_INCREMENT). Noen databaser gir UUID/GUID som en mulig datatype for surrogatnøkler (for eksempel PostgreSQL UUID eller SQL Server UNIQUEIDENTIFIER).

Å ha nøkkelen uavhengig av alle andre kolonner isolerer databaserelasjonene fra endringer i databasedesign eller dataverdier (slik at databasen blir mer smidig), og garanterer unikhet.

I en temporal database er det nødvendig å skille mellom surrogatnøkkelen og forretningsnøkkelen. Hver rad ville ha både en forretningsnøkkel og en surrogatnøkkel. Surrogatnøkkelen identifiserer én unik rad i databasen, mens forretningsnøkkelen identifiserer én unik entitet i den modellerte verdenen. Én tabellrad representerer et tidspunkt som inneholder alle entitetens attributter for et definert tidsrom. Disse skivene viser hele levetiden til én forretningsentitet. For eksempel kan en tabell employee_contracts inneholde temporal informasjon for å holde styr på avtalt arbeidstid. Forretningsnøkkelen for én kontrakt vil være identisk (ikke-unik) i begge rader, men surrogatnøkkelen for hver rad er unik.

Surrogatnøkkel	Naturlig_nøkkel	Ansattnavn	Arbeidstimer_per_uke	Rad_gyldig_fra	Rad_gyldig_til
1	BOS0120	Martin Johnsen	40	2000-01-01	2000-12-31
56	P0000123	Daniel Johnsen	25	1999-01-01	2011-12-31
234	BOS0120	Martin Johnsen	35	2001-01-01	2009-12-31

Noen databasedesignere bruker surrogatnøkler systematisk uavhengig av egnetheten til andre kandidatnøkler, mens andre vil bruke en nøkkel som allerede finnes i dataene (forretningsnøkler) dersom det er mulig.

En surrogatnøkkel kalles noen ganger for "systemgenerert nøkkel", men dette beskriver bare måten man genererer surrogatverdier og ikke surrogatkonseptets natur.

Tilnærminger til å generere surrogater inkluderer:

• Universelt unike identifikatorer (UUID-er)
• Globalt unike identifikatorer (GUID-er)
• Objektidentifikatorer (OID-er)
• Sybase eller SQL Server sine identitetskolonner IDENTITY eller IDENTITY(n,n)
• Oracle sin SEQUENCE, eller GENERATED AS IDENTITY (fra og med versjon 12.1)
• SQL Server sin SEQUENCE (fra og med SQL Server 2012)
• PostgreSQL eller IBM Informix sin SERIAL
• MySQL sin AUTO_INCREMENT
• SQLite sin INTEGER PRIMARY KEY (hvis AUTOINCREMENT brukes vil det forhindre gjenbruk av tall som allerede er brukt, men som er tilgjengelige)
• Microsoft Access sin AutoNumber-datatype
• IBM Db2 og PostgreSQL sin AS IDENTITY GENERATED BY DEFAULT
• Teradata sin identitetskolonne (implementert i DDL )
• Tabellsekvens når sekvensen beregnes ved hjelp av en prosedyre og en løpenummertabell med feltene: id, sekvensnavn, sekvensverdi og inkrementverdi

Fordeler

Stabilitet

Surrogatnøkler endres vanligvis ikke så lenge raden eksisterer. Dette har følgende fordeler:

• Applikasjoner mister ikke referansen til en rad i databasen (siden identifikatoren ikke endres)
• Dataene som er koblet med den primære eller naturlige nøkkelen kan alltid endres, selv med databaser som ikke støtter overlappende oppdateringer på tvers av relaterte fremmednøkler

Kravendringer

Attributter som unikt identifiserer en entitet kan endres, noe som kan gjør naturlige nøkler ugyldige og dermed lite egnet. Anta følgende eksempel:

En ansatts brukernavn er valgt som en naturlig nøkkel. Ved sammenslåing med annet selskap skal ansattlistene slås sammen, og noen av brukernavnene kommer i konflikt.

I disse tilfellene må som regel et nytt attributt legges til den naturlige nøkkelen (for eksempel en opprinnelig_selskap-kolonne). Med en surrogatnøkkel derimot må kun tabellen som definerer surrogatnøkkelen endres. Med naturlige nøkler ville alle tabeller (og muligens annen relatert programvare) som bruker den naturlige nøkkelen måtte endres.

Noen problemdomener har heller ikke en tydelig passende naturlig nøkkel. Ved å bruke surrogatnøkler unngår man å velge en naturlig nøkkel som kan være feil.

Ytelse

Surrogatnøkler er ofte en kompakt datatype, for eksempel et fire-biters heltall. Dette gjør det mulig for databasen å spørre en enkelt nøkkelkolonne raskere sammenlignet med nøkler fordelt over flere kolonner. Videre fører en ikke-redundant fordeling av nøkler til at den resulterende b-treindeksen blir fullstendig balansert. Surrogatnøkler krever også mindre beregningskraft for å skjøte enn en sammensatt nøkkel (på grunn av færre kolonner å sammenligne).

Kompatibilitet

Når man bruker flere systemer for å utvikle databaseapplikasjoner, drivere og objektrelasjonell avbilding, for eksempel Ruby on Rails eller Hibernate, er det mye enklere å bruke surrogatnøkler som er heltall eller GUID-er for hver tabell i stedet for å brke naturlige nøkler, for å enklere støtte database- systemagnostiske operasjoner og objekt-til-rad-avbilding.

Ensartethet

Når hver tabell har en enhetlig surrogatnøkkel kan enkelte oppgaver enkelt automatiseres ved å skrive koden på en tabelluavhengig måte.

Validering

Det er mulig å designe nøkkelverdier som følger et velkjent mønster eller en struktur som kan verifiseres automatisk. For eksempel kan nøklene som er ment å brukes i en kolonne i en tabell være utformet for å se "annerledes ut" enn de som er ment å brukes i en annen kolonne eller tabell. Dette kan gjøre det enklere å oppdage applikasjonsfeil hvor nøklene har blitt feilplassert. Imidlertid bør denne egenskapen til surrogatnøklene aldri brukes til å drive logikken til selve applikasjonene, siden dette vil bryte med prinsippene for databasenormalisering.

Ulemper

Disassosiasjon

Verdiene til genererte surrogatnøkler har ingen sammenheng med den virkelige betydningen av dataene på raden. Når man inspiserer en rad med fremmednøkkelreferanse til en annen tabell via surrogatnøkkel kan ikke betydningen av surrogatnøkkelens rad tydes utifra selve nøkkelen. Hver fremmednøkkel må skjøtes for å se det relaterte dataelementet. Hvis det ikke er satt hensiktsmessige databasebegrensninger eller hvis data er importert fra et eldre system der referanseintegritet ikke ble brukt er det mulig å ha en fremmednøkkelverdi som ikke tilsvarer en primærnøkkelverdi, og derfor er ugyldig. (I denne sammenheng anser Christipher John Date "meningsløsheten" til surrogatnøkler som en fordel.)

For å oppdage slike feil må man kjøre en spørring med venstre ytterskjøt mellom tabellen med fremmednøkkelen og tabellen med primærnøkkelen, som viser begge nøkkelfeltene og eventuelle andre felter som kreves for å skille oppføringene. Alle ugyldige fremmednøkkelverdier vil ha nullverdi i primærnøkkelkolonnen. Behovet for å gjøre en slik sjekk er så vanlig at Microsoft Access faktisk har en egen veiviserfunksjon kalt "Find Unmatched Query" som genererer riktig SQL etter å ha ledet brukeren gjennom en dialogboks. (Det er imidlertid ikke så vanskelig å lage slike spørringer manuelt.) "Find Unmatched"-spørringer brukes vanligvis som en del av en datavask når man arver eldre data.

Surrogatnøkler er unaturlige for data som eksporteres og deles. En spesiell vanskelighet er hvis tabeller fra to ellers identiske skjemaer (for eksempel et testskjema og et utviklingsskjema) kan inneholde oppføringer som er identiske for forretningen, men som har forskjellige nøkler. Dette problemet kan reduseres ved ikke å eksportere surrogatnøkler, bortsett fra som transiente data (for eksempel ved kjøring av applikasjoner som har en aktiv tilkoblingsøkt til databasen).

Når surrogatnøkler erstatter naturlige nøkler vil domenespesifikk referanseintegritet bli kompromittert. For eksempel, i en kunde-grunndatatabell kan den samme kunden ha flere oppføringer under separate kunde-ID-er, selv om den naturlige nøkkelen (en kombinasjon av kundenavn, fødselsdato og e-postadresse) vil være unik. For å hindre kompromittering må den naturlige nøkkelen til tabellen ikke erstattes, men må bevares som en unikhetsbegrensning som implementeres som en unik indeks på kombinasjonen av naturlige nøkkelfelt.

Spørringsoptimering

Relasjonsdatabaser antar at en unik indeks brukes på en tabells primærnøkkel. Den unike indeksen har to formål:

1. Håndheve entitetsintegritet, siden primærnøkkeldata må være unike på tvers av rader
2. Gi raskt søke etter rader når de etterspørres

Siden surrogatnøkler erstatter en tabells identifiserende attributter (den naturlige nøkkelen), og siden de identifiserende attributtene sannsynligvis er de som er forespurt, blir spørringsoptimereren tvunget til å kjøre full tabellskanning på sannsynlige spørringer. En løsning for full tabellskann er å bruke indekser på identifiserende attributter eller delmengder av dem. Der hvor slike mengder i seg selv er en kandidatnøkkel kan indeksen være en unik indeks.

Disse ekstra indeksene vil imidlertid ta opp lagringsplass og senke hastigheten på innsettinger og slettinger.

Normalisering

Surrogatnøkler kan resultere i dupliserte verdier i alle naturlige nøkler . For å hindre duplisering må man bevare rollen til de naturlige nøklene som unikhetsbegrensninger når man definerer tabellen ved å bruke enten SQL-uttrykket CREATE TABLE eller (i tilfelle begrensningene er lagt til som en ettertanke) ALTER TABLE ... ADD CONSTRAINT.

Modellering av forretningsprosesser

Siden surrogatnøkler er unaturlige kan det oppstå feil ved modellering av forretningskravene. Forretningskrav basert på den naturlige nøkkelen må da oversettes til surrogatnøkkelen. En strategi er å trekke et klart skille mellom den logiske modellen (der surrogatnøkler ikke vises) og den fysiske implementeringen av den modellen for å sikre at den logiske modellen er korrekt og rimelig godt normalisert, og sikre at den fysiske modellen er en korrekt implementering av den logiske modellen.

Utilsiktet avsløring

Proprietær informasjon kan lekkes hvis surrogatnøkler genereres sekvensielt. Ved å ta differansen mellom en tidligere generert sekvensiell nøkkel fra en nylig generert sekvensiell nøkkel kan man beregne antall rader som er satt inn i løpet av den tidsperioden. Dette kan eksponere for eksempel antall transaksjoner eller nye kontoer per periode. Et lignende problem er Det tyske stridsvognproblemet, som historisk gikk ut på å forutsi tysk produksjon av stridsvogner i andre verdenskrig basert på stigende serienumre på tyske stridsvogner tapt i kamp. Mer generelt kan man si at dette problemet går ut på å estimere maksimum av en diskret uniform fordeling fra prøvetaking uten tilbakelegging.

Det er noen måter å løse dette problemet på:

• Øke sekvensnummeret tilfeldig
• Generere en tilfeldig nøkkel som en UUID

Utilsiktede antakelser

Sekvensielt genererte surrogatnøkler kan innebære at hendelser med en høyere nøkkelverdi skjedde etter hendelser med en lavere verdi. Dette er ikke nødvendigvis sant, fordi slike verdier ikke garanterer tidssekvens, siden det er mulig at innsettinger kan mislykkes og etterlate hull som kan fylles av andre insettinger på et senere tidspunkt. Hvis kronologi er viktig må dato og klokkeslett registreres separat.

Se også

• Naturlig nøkkel
• Objektidentifikator
• Vedvarende objektidentifikator