For enhver virksomhet er data essensielt, og evnen til å håndtere og lagre databaser gir stor verdi. Databaser er strukturert på en bestemt måte, slik at data kan organiseres i sammenhenger. Dette bringer oss til relasjonsdatabaser, som har vært en sentral del av databehandling siden 1970-tallet. Relasjonsdatabaser er fortsatt foretrukket i dag på grunn av deres effektivitet ved datahåndtering.
Selv om mange relasjonsdatabaser er tilgjengelige, er MySQL en av de ledende. Ifølge Statista var den rangert som nummer to globalt i januar 2022.
I SQL-servere defineres begrensninger som regler som gjelder for en eller flere kolonner. Disse begrensningene er knyttet til verdiene i kolonnen og bidrar til å opprettholde dataintegritet, nøyaktighet og pålitelighet.
Kort sagt, bare data som oppfyller de definerte begrensningene, kan legges inn i kolonnen. Hvis dataene ikke samsvarer med kriteriene, vil innsettingsprosessen mislykkes.
Dette innlegget forutsetter at du har kjennskap til relasjonsdatabaser, spesielt MySQL, og er interessert i å utvide din kunnskap. Vi vil også se nærmere på hvordan man interagerer med fremmednøkkelbegrensninger.
Primærnøkkelbegrensninger – en oppsummering
En SQL-tabell inneholder en eller flere kolonner med nøkkelverdier som unikt identifiserer hver rad. Disse kolonnene, kjent som primærnøkler (PK), sikrer tabellens entitetsintegritet. Primærnøkkelbegrensninger garanterer unikhet og defineres ofte i en identitetskolonne.
Når en primærnøkkelbegrensning defineres, vil databasemotoren automatisk pålegge dataunikhet ved å generere unike indekser for hver primærkolonne. Primærnøkler gir også rask datatilgang når de brukes i spørringer.
Hvis primærnøkkelbegrensningen defineres på flere kolonner, kalles dette en sammensatt primærnøkkel. I dette tilfellet kan hver kolonne i primærnøkkelen ha dupliserte verdier, men kombinasjonen av verdiene må være unik.
Et eksempel er en tabell med kolonnene «id», «navn» og «alder». Hvis primærnøkkelbegrensningen defineres på kombinasjonen av `id` og `navn`, kan du ha duplikater av `id`- eller `navn`-verdier. Likevel må hver kombinasjon være unik. Du kan ha poster med «id=1» og «navn=Walter» og «alder=22», og en annen med «id=1», «navn=Henry» og «alder=27». Men du kan ikke ha en annen post med `id=1` og `navn=Walter`, fordi denne kombinasjonen allerede finnes.
Her er noen viktige punkter å huske:
- En tabell kan bare ha én primærnøkkelbegrensning.
- Primærnøkler kan ikke overstige 16 kolonner og en maksimal lengde på 900 tegn.
- Indeksene generert av primærnøkler kan øke indekser i tabellen. En tabell kan ikke ha mer enn 1 gruppert indeks, og maksimalt 999 ikke-grupperte indekser.
- Hvis gruppert eller ikke-gruppert ikke er spesifisert for en nøkkelbegrensning, blir gruppert valgt automatisk.
- Alle kolonner definert i en primærnøkkelbegrensning må være definert som ikke-null. Hvis ikke, vil alle kolonnene i begrensningen automatisk bli satt til ikke-null.
- Når primærnøkler defineres på en brukerdefinert kolonnetype (CLR), må typeimplementeringen støtte binær sortering.
Fremmednøkkelbegrensninger – en oversikt
En fremmednøkkel (FK) er en kolonne eller en kombinasjon av kolonner som brukes til å etablere og koble sammen to tabeller. Den styrer dataene som kan lagres i en fremmednøkkeltabell.
En fremmednøkkelreferanse opprettes når en eller flere kolonner i en tabell refererer til primærnøkkelkolonnen(e) i en annen tabell.
Dette skaper en kobling mellom to tabeller, hvor en kolonne eller kolonner i en tabell refererer til primærnøkkelen i den andre tabellen.
Et praktisk eksempel er en tabell `Salg.SalesOrderHeader` med en fremmednøkkel som refererer til `Salg.Person`-tabellen. Dette illustrerer det logiske forholdet mellom selgere og salgsordrer.
I dette tilfellet kobles `SalesPersonID`-en i kolonnen `SalesOrderHeader` til primærnøkkelkolonnen i `SalesPerson`-tabellen. Fremmednøkkelen i `SalesPerson`-tabellen er `SalesPersonID`-kolonnen i `SalesOrderHeader`.
Denne relasjonen definerer en regel: en `SalesPersonID`-verdi kan ikke eksistere i `SalesOrderHeader`-tabellen med mindre den finnes i `SalesPerson`-tabellen.
En tabell kan referere til opptil 253 andre kolonner og tabeller som fremmednøkler, også kjent som utgående referanser. Siden 2016 har SQL-serveren økt antall tabeller og kolonner du kan referere til i en enkelt tabell, kalt innkommende referanser, fra 253 til 10 000. Denne økningen har imidlertid noen begrensninger:
- Fremmednøkkelreferanser som overstiger 253, er kun tilgjengelig for `DELETE DML`-operasjoner. `MERGE` og `UPDATE` støttes ikke.
- Tabeller som har fremmednøkkelreferanser til seg selv er begrenset til maks 253 fremmednøkkelreferanser.
- For kolonneindekser, minneoptimaliserte tabeller og partisjonerte fremmednøkkeltabeller, er fremmednøkkelreferansene begrenset til 253.
Hva er fordelene med fremmednøkler?
Fremmednøkkelbegrensninger spiller en viktig rolle i å sikre integriteten og konsistensen til data i en relasjonsdatabase. Her er en oversikt over hvorfor fremmednøkkelbegrensninger er essensielle.
- Referanseintegritet: Fremmednøkkelbegrensninger sikrer at hver underordnet tabellpost samsvarer med en post i hovedtabellen, noe som sikrer datakonsistens mellom begge tabellene.
- Unngå foreldreløse poster: Hvis en hovedtabell slettes, vil fremmednøkkelbegrensningene sørge for at også den tilknyttede underordnede tabellen slettes, og dermed forhindre foreldreløse poster som kan føre til datainkonsistens.
- Forbedret ytelse: Fremmednøkkelbegrensninger forbedrer søkeytelsen ved at databasebehandlingssystemet kan optimalisere spørringer basert på tabellrelasjoner.
Indekser for fremmednøkkelbegrensninger
Fremmednøkkelbegrensninger genererer ikke automatisk indekser på samme måte som primærnøkler. Det er fordelaktig å opprette indekser manuelt for fremmednøkkelbegrensninger av følgende grunner:
- Fremmednøkkelkolonner brukes ofte som sammenføyningskriterier når man kombinerer data fra relaterte tabeller i spørringer ved å matche kolonnene som er knyttet til begrensningen. Indekser hjelper databasen med å finne tilknyttede data i en fremmed tabell.
- Hvis du endrer primærnøkkelbegrensninger, sjekkes de opp mot de fremmede i relaterte tabeller.
Det er ikke obligatorisk å opprette indekser. Du kan fortsatt kombinere data fra to tabeller uten å definere primær- og fremmednøkkelbegrensninger. Men å legge til fremmednøkkelbegrensninger optimaliserer tabellene og sammenføyninger i spørringer som bruker nøklene. Hvis primærnøkkelbegrensninger endres, sjekkes de opp mot de fremmede i relaterte tabeller.
Tips for å opprette fremmednøkkelbegrensninger i SQL
La oss nå fokusere på hvordan du kan opprette fremmednøkkelbegrensninger.
Et fremmednøkkelfelt i en tabell refererer til primærnøkkelen i en annen tabell. Tabellen med primærnøkkelen er den overordnede tabellen, og tabellen med fremmednøkkelen er den underordnede tabellen. La oss se nærmere på det.
Opprette en fremmednøkkel mens du oppretter en tabell
Når du oppretter en tabell, kan du samtidig opprette en fremmednøkkelbegrensning for å opprettholde referanseintegritet. Slik gjør du det:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Koden ovenfor oppretter en tabell kalt «ordrer» med den primære heltallsnøkkelen «ordre_id», et annet heltall «customer_id» og datoen «order_date». I dette tilfellet legges FOREIGN KEY-begrensningen til «customer_id»-kolonnen og refererer til «customer_id» i «customer»-tabellen.
Opprette en fremmednøkkel etter å ha opprettet en tabell
Hvis du allerede har opprettet en tabell og ønsker å legge til en fremmednøkkelbegrensning, kan du bruke `ALTER TABLE`-setningen. Se på kodebiten nedenfor.
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
Her har du lagt til en «customer_id»-kolonne for fremmednøkkel i «orders»-tabellen som refererer til «customer_id»-kolonnen i «customers»-tabellen.
Opprette en fremmednøkkel uten å sjekke eksisterende data
Når du legger til en fremmednøkkelbegrensning i en tabell, vil databasen automatisk sjekke eksisterende data for å sikre at de samsvarer med begrensningen. Men hvis du vet at dataene er konsistente og ønsker å legge til begrensningen uten en konsistenssjekk, kan du gjøre dette:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) NOT VALIDATE;
`NOT VALIDATE`-kommandoen forteller databasen at den ikke skal sjekke eksisterende data. Dette er nyttig i spesielle tilfeller, for eksempel når du har store mengder data og ønsker å fullføre valideringsprosessen raskere.
Opprette en fremmednøkkel via DELETE/UPDATE
Når du oppretter fremmednøkkelbegrensninger, kan du styre handlingene som skal utføres når den refererte raden oppdateres eller slettes. Du kan bruke overlappende referanseintegritetsbegrensninger for å styre disse handlingene. Dette inkluderer:
#1. INGEN HANDLING
Som i mange andre databaser, er «INGEN HANDLING» standard oppførsel når du oppretter en fremmednøkkelbegrensning. Dette betyr at ingen handling utføres når den refererte raden slettes eller oppdateres.
Databasemotoren vil vise en feilmelding hvis fremmednøkkelbegrensningen brytes. Dette anbefales imidlertid ikke, da det kan føre til problemer med referanseintegritet, ettersom fremmednøkkelbegrensningen må håndheves. Her er et eksempel på hvordan du gjør dette:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
#2. CASCADE
«CASCADE»-regelen er et annet alternativ for handlingene «ON DELETE» og «ON UPDATE» når du oppretter fremmednøkkelbegrensninger. Når denne regelen er aktiv, betyr det at når en rad oppdateres eller slettes i hovedtabellen, vil de tilhørende refererte radene også bli oppdatert eller slettet. Denne teknikken er nyttig for å opprettholde referanseintegritet. Her er et eksempel:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE;
Du bør være forsiktig når du bruker denne regelen, da den kan føre til uønskede konsekvenser hvis den ikke brukes riktig. Du vil unngå å slette for mye data ved et uhell eller lage sirkulære referanser. Bruk derfor dette alternativet kun om nødvendig og med forsiktighet.
Det er noen regler for bruk av CASCADE:
- Du kan ikke spesifisere CASCADE hvis en tidsstempelkolonne er del av fremmed- eller referert nøkkel.
- Hvis tabellen din har en «INSTEAD OF DELETE»-trigger, kan du ikke spesifisere «ON DELETE CASCADE».
- Du kan ikke spesifisere «ON UPDATE CASCADE» hvis tabellen har en «INSTEAD OF UPDATE»-trigger.
#3. SET NULL
Når du sletter eller oppdaterer en tilsvarende rad i hovedtabellen, settes alle verdier som utgjør fremmednøkkelen til null. Denne begrensningen krever at fremmednøkkelkolonnene tillater nullverdier, og den kan ikke brukes for tabeller som har «INSTEAD OF UPDATE»-triggere. Her er et eksempel på hvordan du gjør det.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL ON UPDATE SET NULL
Her har du satt fremmednøkkelkolonnen «customer_id» i «ordre»-tabellen til å være null dersom den tilhørende raden i «kunder»-tabellen slettes eller oppdateres.
#4. SET DEFAULT
Her setter du alle verdiene som utgjør fremmednøkkelen til en standardverdi, forutsatt at den refererte raden i hovedtabellen oppdateres eller slettes.
Denne begrensningen vil fungere hvis alle fremmednøkkelkolonnene har standarddefinisjoner. Hvis en kolonne tillater nullverdier, vil standardverdien settes til NULL. Merk at dette alternativet ikke kan brukes for tabeller med «INSTEAD OF UPDATE»-triggere. Her er et eksempel:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
I eksemplet over har du satt «customer_id» i «ordre»-tabellen til standardverdien når den tilhørende raden i «kunder»-tabellen slettes eller oppdateres.
Avsluttende ord
I denne veiledningen har vi sett på primærnøkkelbegrensninger og gått i dybden på fremmednøkkelbegrensninger. Vi har også gjennomgått flere måter å lage fremmednøkkelbegrensninger på. Selv om det finnes flere måter å opprette fremmednøkkelbegrensninger, har dette innlegget klargjort noen av metodene.
Håpet er at du har lært deg noen nye teknikker, og husk at du ikke er begrenset til å bruke dem enkeltvis. For eksempel kan «CASCADE», «SET NULL», «SET DEFAULT» og «NO ACTION» kombineres i tabeller med referanserelasjoner.
Hvis tabellen din møter «NO ACTION», vil den falle tilbake på de andre begrensningene. I andre tilfeller kan en «DELETE»-handling utløse en kombinasjon av disse reglene, hvor «NO ACTION» utføres til slutt.
Du kan også sjekke ut SQL-juksearket for mer informasjon.