SQL Server FOREIGN KEY: Hvordan opprette med eksempel

by: Fiona Brown Fiona Brown
Hours oppdatert

⚡ Smart oppsummering

En fremmednøkkel i SQL Server håndhever referanseintegritet ved å koble en underordnet tabell til en overordnet tabell. Hver fremmednøkkelverdi må allerede finnes i den refererte primærnøkkelen til den overordnede tabellen.

  • 🔗 Hva er en fremmednøkkel: En fremmednøkkel kobler en underordnet tabell til en overordnet tabell og håndhever referanseintegritet mellom dem.
  • 👪 Foreldre og barn: Den refererte tabellen er den overordnede tabellen; tabellen som inneholder fremmednøkkelen er den underordnede tabellen, som peker til den overordnede primærnøkkelen.
  • 🖱️ To opprettelsesmetoder: Både SQL Server Management Studio-relasjoner og T-SQL CREATE TABLE … FOREIGN KEY … REFERENCES-klausulen definerer en fremmednøkkel.
  • ➕ Legg til i en eksisterende tabell: ENDRE TABELL … LEGG TIL BEGRENSNING … UTENLANDSK NØKKEL legger til relasjonen til en tabell som allerede finnes.
  • 🔄 Referansehandlinger: ON DELETE- og ON UPDATE-klausuler styrer underrader med NO ACTION, CASCADE, SET NULL eller SET DEFAULT.
  • ✅ Integrity sjekk: Å sette inn en underrad der nøkkelen ikke har noen samsvarende overordnet rad blir avvist, keeping dataene er konsistente.
Les mer

SQL Server FOREIGN KEY: Hvordan opprette i SQL Server med et eksempel

Hva er en utenlandsnøkkel?

En fremmednøkkel gir en måte å håndheve referanseintegritet på SQL ServerEnkelt sagt sikrer en fremmednøkkel at verdier i én tabell må være tilstede i en annen tabell.

Regler for UTENLANDSK NØKKEL

  • NULL er tillatt i en SQL-fremmednøkkel.
  • Tabellen det refereres til kalles overordnet tabell.
  • Tabellen med fremmednøkkelen kalles barnetabellen.
  • Fremmednøkkelen i barnetabellen refererer til primærnøkkel i den overordnede tabellen.
  • Dette foreldre-barn-forholdet håndhever regelen kjent som «referanseintegritet».

Diagrammet nedenfor oppsummerer alle punktene ovenfor for fremmednøkkelen.

Diagram over en fremmednøkkel som kobler en underordnet tabell til primærnøkkelen til den overordnede tabellen

Hvordan lage utenlandsk nøkkel i SQL

Du kan opprette en fremmednøkkel i SQL Server på to måter:

SQL Server Management Studio

Overordnet tabell: La oss si at vi har en eksisterende overordnet tabell med navnet «Kurs». Kurs-ID og Kursnavn er to kolonner, med Kurs-ID som primærnøkkel.

Overordnet tabell Kurs med primærnøkkelen Course_Id og kolonnene Course_name

Undertabell: Vi må opprette den andre tabellen som en undertabell. 'Course_ID' og 'Course_Strength' er de to kolonnene. 'Course_ID' skal imidlertid være fremmednøkkelen.

Trinn 1) Høyreklikk på Tabeller > Ny > Tabell…

Høyreklikk på Tabeller, deretter Ny og deretter Tabell i SQL Server Management Studio

Trinn 2) Skriv inn to kolonnenavn som «Course_ID» og «Course_Strength». Høyreklikk på «Course_Id»-kolonnen, og klikk deretter på Relasjon.

Nye underordnede tabellkolonner Course_ID og Course_Strength med Relasjonsmenyen

Trinn 3) Klikk på «Legg til» i «Foreign Key Relations».

Dialogboksen Forhold mellom fremmednøkler med Legg til-knappen

Trinn 4) Klikk på ikonet «…» i «Tabell- og kolonnespesifikasjon».

Spesifikasjonsfelt for tabeller og kolonner med ellipseknappen

Trinn 5) Velg «Primærnøkkeltabell» som «KURS» og den nye tabellen som opprettes som «Fremmednøkkeltabell» fra rullegardinmenyen.

Velge COURSE som primærnøkkeltabell i relasjonsdialogen

Trinn 6) For «Primærnøkkeltabell» velger du kolonnen «Kurs-ID» som kolonne for primærnøkkeltabellen.

For «Tabell med fremmednøkler» velger du kolonnen «Kurs-ID» som kolonne for tabellen med fremmednøkler. Klikk på OK.

kartping Course_Id som både primærnøkkel- og fremmednøkkelkolonner

Trinn 7) Klikk på Legg til.

Klikk på Legg til for å bekrefte forholdet mellom fremmednøkkelen

Trinn 8) Gi tabellen navnet «Course_Strength» og klikk OK.

Navngi undertabellen Course_Strength og klikk OK

Resultat: Vi har satt en foreldre-barn-relasjon mellom 'Kurs' og 'Kursstyrke'.

Foreldre-barn-forhold etablert mellom Kurs og Kursstyrke

RELATERTE ARTIKLER

T-SQL: Opprett en overordnet-underordnet tabell ved hjelp av T-SQL

Overordnet tabell: Tenk på at vi har en eksisterende overordnet tabell med navnet «Kurs». Kurs-ID og Kursnavn er to kolonner, med Kurs-ID som primærnøkkel.

Eksisterende overordnet tabell Kurs med Kurs-ID som primærnøkkel

Undertabell: Vi må opprette den andre tabellen som undertabellen med navnet «Course_Strength_TSQL». «Course_ID» og «Course_Strength» er de to kolonnene. Imidlertid skal «Course_ID» være fremmednøkkelen.

Nedenfor er syntaksen til lage en tabell med en UTENLANDSK NØKKEL.

Syntaks:

CREATE TABLE childTable
(
  column_1 datatype [ NULL |NOT NULL ],
  column_2 datatype [ NULL |NOT NULL ],
  ...

  CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n)
    [ ON DELETE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ]
    [ ON UPDATE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ] 
);

Her er en beskrivelse av parameterne ovenfor:

  • childTable er navnet på tabellen som skal opprettes.
  • kolonne_1 og kolonne_2 er kolonnene som skal legges til i tabellen.
  • fkey_name er navnet på fremmednøkkelbegrensningen som skal opprettes.
  • barnekolonne1, barnekolonne2 … barnekolonne_n er underordnede tabellkolonner som refererer til primærnøkkelen i overordnet tabell.
  • parentTable er navnet på den overordnede tabellen hvis nøkkel det refereres til i barnetabellen.
  • parent_column1, parent_column2 … parent_column_n er kolonnene som utgjør primærnøkkelen til den overordnede tabellen.
  • ON DELETE er en valgfri parameter som angir hva som skjer med underdataene etter at overdataene er slettet. Verdier inkluderer NO ACTION, SET NULL, CASCADE eller SET DEFAULT.
  • ON UPDATE er en valgfri parameter som angir hva som skjer med underdataene etter at overdataene er oppdatert. Verdier inkluderer NO ACTION, SET NULL, CASCADE eller SET DEFAULT.
  • INGEN HANDLING betyr at ingenting skjer med underdataene etter at overordnede data er oppdatert eller slettet.
  • CASCADE betyr at underdataene slettes eller oppdateres etter at overordnede data er slettet eller oppdatert.
  • SET NULL betyr at underdataene settes til null etter at overordnede data er oppdatert eller slettet.
  • ANGI STANDARD betyr at underdataene settes til standardverdien etter en oppdatering eller sletting av de overordnede dataene.

La oss se på et eksempel på en fremmednøkkel som oppretter en tabell med én kolonne som en fremmednøkkel, ved hjelp av en data-type for hver kolonne.

Fremmednøkkel i SQL-eksempel

Spørsmål:

CREATE TABLE Course_Strength_TSQL
(
Course_ID Int,
Course_Strength Varchar(20) 
CONSTRAINT FK FOREIGN KEY (Course_ID)
REFERENCES COURSE (Course_ID)	
)

Trinn 1) Kjør spørringen ved å klikke på Utfør.

Utfører CREATE TABLE-spørringen som definerer fremmednøkkelen Course_ID

Resultat: Vi har satt en foreldre-barn-relasjon mellom 'Kurs' og 'Course_Strength_TSQL'.

Foreldre-barn-forhold opprettet mellom Course og Course_Strength_TSQL

Bruker ALTER TABLE

Nå skal vi lære hvordan man legger til en fremmednøkkel i SQL Server til en tabell som allerede finnes ved hjelp av ALTER TABLE-setningen. Vi bruker syntaksen nedenfor:

ALTER TABLE childTable
ADD CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n);

Her er en beskrivelse av parameterne som er brukt ovenfor:

  • childTable er navnet på tabellen som skal opprettes.
  • kolonne_1 og kolonne_2 er kolonnene som skal legges til i tabellen.
  • fkey_name er navnet på fremmednøkkelbegrensningen som skal opprettes.
  • barnekolonne1, barnekolonne2 … barnekolonne_n er underordnede tabellkolonner som refererer til primærnøkkelen i overordnet tabell.
  • parentTable er navnet på den overordnede tabellen hvis nøkkel det refereres til i barnetabellen.
  • parent_column1, parent_column2 … parent_column_n er kolonnene som utgjør primærnøkkelen til den overordnede tabellen.

Eksempel på å legge til fremmednøkkel i ALTER TABLE:

ALTER TABLE department
ADD CONSTRAINT fkey_student_admission
    FOREIGN KEY (admission)
    REFERENCES students (admission);

Vi har laget en fremmednøkkel som heter fkey_student_admission på avdelingstabellen. Denne fremmednøkkelen refererer til opptakskolonnen i elevtabellen.

Eksempel på forespørsel UTENLANDSK NØKKEL

La oss først se på dataene i overordnet tabell, COURSE.

Spørsmål:

SELECT * from COURSE;

SELECT-resultat som viser den overordnede tabellen COURSE-data

La oss nå sette inn noen rader i barnetabellen «Course_Strength_TSQL». Vi skal prøve å sette inn to typer rader:

  • Den første typen, der Course_Id i barnetabellen finnes i Course_Id i foreldretabellen, det vil si at Course_Id = 1 og 2.
  • Den andre typen, der Course_Id i barnetabellen ikke finnes i Course_Id i foreldretabellen, det vil si at Course_Id = 5.

Spørsmål:

Insert into COURSE_STRENGTH values (1,'SQL');
Insert into COURSE_STRENGTH values (2,'Python');
Insert into COURSE_STRENGTH values (5,'PERL');

Setter inn underrader, inkludert Course_ID 5 som ikke har noen samsvarende overordnet rad

Resultat: La oss kjøre spørringen sammen for å se foreldre- og underordnetabellene våre.

Radene med Course_ID 1 og 2 finnes i Course_Strength-tabellen. Course_ID 5 er imidlertid et unntak, fordi den ikke har noen samsvarende rad i den overordnede tabellen.

Foreldre- og underordnede tabeller sammenlignet; Course_ID 5 bryter med referanseintegritet

Spørsmål og svar

En primærnøkkel identifiserer unikt hver rad i sin egen tabell og kan ikke være NULL. En fremmednøkkel refererer til den primærnøkkelen fra en annen tabell for å håndheve referanseintegritet. primærnøkkel kontra fremmednøkkel Sammenligning forklarer alle forskjeller.

Ja. En fremmednøkkel kan referere til enten en primærnøkkel eller en hvilken som helst kolonne som har en UNIK begrensning i den overordnede tabellen. Den refererte kolonnen må inneholde unike verdier, slik at hver underordnede rad samsvarer nøyaktig med én overordnet rad.

VED SLETTING sletter CASCADE automatisk de samsvarende underradene når den overordnede raden slettes.ping tabellene er konsistente. Alternativer er SET NULL, som fjerner den underordnede fremmednøkkelen, og NO ACTION, som blokkerer slettingen.

Ja. En selvrefererende fremmednøkkel peker til en primærnøkkel i samme tabell, som modellerer hierarkier, for eksempel en ansattrad som refererer til sin leder. For selvreferanser anbefaler SQL Server ON DELETE NO ACTION for å unngå kaskadesykluser.

Ja, med mindre kolonnen er deklarert som NOT NULL. En NULL-fremmednøkkel betyr at den underordnede raden ikke er koblet til noen overordnet rad ennå, og SQL Server hopper over referenssjekken for den NULL-verdien.

Kjør ALTER TABLE child_table DROP CONSTRAINT fkey_name. Du må oppgi begrensningsnavnet, som du finner i sys.foreign_keys. Dropping Fremmednøkkelen fjerner relasjonen, men lar både tabellene og dataene deres være uendret.

Ja. GitHub Copilot kan skrive FOREIGN KEY-begrensninger i CREATE TABLE- eller ALTER TABLE-setninger fra en naturlig språkledetekst og foreslå den overordnede tabellen og den refererte kolonnen. Se alltid gjennom nøklene, referansehandlingene og datatypene før du kjører skriptet.

AI- og maskinlæringsverktøy undersøker eksempeldata og spørremønstre for å foreslå hvilke kolonner som skal bli fremmednøkler, oppdage manglende eller foreldreløse relasjoner og anbefale passende ON DELETE-handlinger. Utvikleren gjennomgår hvert forslag før det tas i bruk.

Oppsummer dette innlegget med: