SQL Server FOREIGN KEY: Hur man skapar med exempel

By: Redaktionellt team Redaktionellt team
Hours Uppdaterad

⚡ Smart sammanfattning

En främmande nyckel i SQL Server framtvingar referensintegritet genom att länka en underordnad tabell till en överordnad tabell. Varje främmande nyckelvärde måste redan finnas i den refererade primärnyckeln i överordnad tabell.

  • 🔗 Vad är en främmande nyckel: En främmande nyckel länkar en underordnad tabell till en föräldertabell och upprätthåller referensintegritet mellan dem.
  • 👪 Förälder och barn: Den refererade tabellen är föräldertabellen; tabellen som innehåller den främmande nyckeln är undertabellen, som pekar på den överordnade primärnyckeln.
  • 🖱️ Två skapandemetoder: Både relationer i SQL Server Management Studio och T-SQL CREATE TABLE … FOREIGN KEY … REFERENCES-klausulen definierar en främmande nyckel.
  • ➕ Lägg till i en befintlig tabell: ÄNDRING AV TABELL … LÄGG TILL BEGRÄNSNING … UTLÄNDSK NYCKEL lägger till relationen till en tabell som redan finns.
  • 🔄 Referensåtgärder: ON DELETE- och ON UPDATE-klausulerna styr underrader med NO ACTION, CASCADE, SET NULL eller SET DEFAULT.
  • ✅ Integrity kontrollera: Att infoga en underrad vars nyckel inte har någon matchande överordnad rad avvisas, keeping uppgifterna är konsekventa.
Läs mer

SQL Server FOREIGN NYCKEL: Hur man skapar i SQL Server med ett exempel

Vad är en UTLÄNDSKNYCKEL?

En främmande nyckel ger ett sätt att upprätthålla referensintegritet inom SQL ServerEnkelt uttryckt säkerställer en främmande nyckel att värden i en tabell måste finnas i en annan tabell.

Regler för UTLÄNDSKNYCKEL

  • NULL är tillåtet i en SQL-främmande nyckel.
  • Tabellen som refereras kallas föräldratabellen.
  • Tabellen med den främmande nyckeln kallas undertabellen.
  • Den främmande nyckeln i undertabellen refererar till primär nyckel i den överordnade tabellen.
  • Denna förälder-barn-relation upprätthåller regeln som kallas "referensintegritet".

Diagrammet nedan sammanfattar alla ovanstående punkter för den främmande nyckeln.

Diagram över en främmande nyckel som länkar en underordnad tabell till den överordnade tabellens primärnyckel

Hur man skapar FOREIGN KEY i SQL

Du kan skapa en främmande nyckel i SQL Server på två sätt:

SQL Server Management Studio

Överordnad tabell: Anta att vi har en befintlig överordnad tabell med namnet "Kurs". Kurs-ID och Kursnamn är två kolumner, med Kurs-ID som primärnyckel.

Överordnad tabell Kurs med primärnyckeln Course_Id och kolumnerna Course_name

Underordnad tabell: Vi behöver skapa den andra tabellen som en underordnad tabell. 'Course_ID' och 'Course_Strength' är dess två kolumner. 'Course_ID' ska dock vara den främmande nyckeln.

Steg 1) Högerklicka på Tabeller > Nytt > Tabell…

Högerklicka på Tabeller, sedan Ny och sedan Tabell i SQL Server Management Studio

Steg 2) Ange två kolumnnamn som 'Course_ID' och 'Course_Strength'. Högerklicka på kolumnen 'Course_Id' och klicka sedan på Relationship.

Nya underordnade tabellkolumner Course_ID och Course_Strength med Relationsmenyn

Steg 3) Klicka på "Lägg till" i "Främmande nyckelrelationer".

Dialogrutan Främmande nyckelrelationer med knappen Lägg till

Steg 4) I 'Specifikation för tabeller och kolumner' klickar du på ikonen '...'.

Specifikationsfält för tabeller och kolumner med ellipsknappen

Steg 5) Välj 'Primärnyckeltabell' som 'KURS' och den nya tabellen som skapas som 'Främmande nyckeltabell' från rullgardinsmenyn.

Välja COURSE som primärnyckeltabell i relationsdialogrutan

Steg 6) För 'Primärnyckeltabell', välj kolumnen 'Course_Id' som primärnyckeltabellkolumn.

För tabellen 'Foreign Key Table' väljer du kolumnen 'Course_Id' som kolumn för tabellen med främmande nycklar. Klicka på OK.

Kartaping Course_Id som både primärnyckel- och främmande nyckelkolumner

Steg 7) Klicka på Lägg till.

Klicka på Lägg till för att bekräfta relationen mellan främmande nyckeln

Steg 8) Ange tabellnamnet 'Course_Strength' och klicka på OK.

Namnge undertabellen Course_Strength och klicka på OK

Resultat: Vi har skapat en förälder-barn-relation mellan 'Kurs' och 'Kursstyrka'.

Förälder-barn-relation etablerad mellan Kurs och Kursstyrka

Missa inte:

T-SQL: Skapa en överordnad-underordnad tabell med hjälp av T-SQL

Överordnad tabell: Tänk på att vi har en befintlig överordnad tabell med namnet "Kurs". Kurs-ID och Kursnamn är två kolumner, med Kurs-ID som primärnyckel.

Befintlig överordnad tabell Kurs med Kurs-ID som primärnyckel

Underordnad tabell: Vi behöver skapa den andra tabellen som underordnad tabell med namnet 'Course_Strength_TSQL'. 'Course_ID' och 'Course_Strength' är dess två kolumner. 'Course_ID' ska dock vara den främmande nyckeln.

Nedan följer syntaxen för att skapa en tabell med en UTLÄNDSK NYCKEL.

Syntax:

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 } ] 
);

Här är en beskrivning av ovanstående parametrar:

  • childTable är namnet på tabellen som ska skapas.
  • kolumn_1 och kolumn_2 är de kolumner som ska läggas till i tabellen.
  • fkey_name är namnet på den främmande nyckelbegränsning som ska skapas.
  • child_column1, child_column2 … child_column_n är de underordnade tabellkolumner som refererar till primärnyckeln i parentTable.
  • parentTable är namnet på den överordnade tabell vars nyckel refereras till i undertabellen.
  • parent_column1, parent_column2 … parent_column_n är kolumnerna som utgör primärnyckeln i överordnade tabellen.
  • ON DELETE är en valfri parameter som anger vad som händer med underdata efter att överdata har raderats. Värdena inkluderar NO ACTION, SET NULL, CASCADE eller SET DEFAULT.
  • ON UPDATE är en valfri parameter som anger vad som händer med underdata efter att överdata har uppdaterats. Värdena inkluderar NO ACTION, SET NULL, CASCADE eller SET DEFAULT.
  • INGEN ÅTGÄRD innebär att ingenting händer med underdata efter att överdata har uppdaterats eller raderats.
  • CASCADE innebär att underdata raderas eller uppdateras efter att överordnade data har raderats eller uppdaterats.
  • SET NULL innebär att underdata sätts till null efter att överordnad data har uppdaterats eller raderats.
  • ANGE STANDARDvärde innebär att underdata ställs in på standardvärdet efter en uppdatering eller borttagning av överordnade data.

Låt oss se ett exempel på en främmande nyckel som skapar en tabell med en kolumn som en UTLÄNDSK NYCKEL med hjälp av en data typ för varje kolumn.

Främmande nyckel i SQL-exempel

Fråga:

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

Steg 1) Kör frågan genom att klicka på Kör.

Köra CREATE TABLE-frågan som definierar den främmande nyckeln Course_ID

Resultat: Vi har ställt in en förälder-barn-relation mellan 'Course' och 'Course_Strength_TSQL'.

Förälder-barn-relation skapad mellan Course och Course_Strength_TSQL

Använder ALTER TABLE

Nu ska vi lära oss hur man lägger till en främmande nyckel i SQL Server till en tabell som redan finns med hjälp av ALTER TABLE-satsen. Vi kommer att använda syntaxen nedan:

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);

Här är en beskrivning av parametrarna som används ovan:

  • childTable är namnet på tabellen som ska skapas.
  • kolumn_1 och kolumn_2 är de kolumner som ska läggas till i tabellen.
  • fkey_name är namnet på den främmande nyckelbegränsning som ska skapas.
  • child_column1, child_column2 … child_column_n är de underordnade tabellkolumner som refererar till primärnyckeln i parentTable.
  • parentTable är namnet på den överordnade tabell vars nyckel refereras till i undertabellen.
  • parent_column1, parent_column2 … parent_column_n är kolumnerna som utgör primärnyckeln i överordnade tabellen.

ALTER TABLE exempel på att lägga till en främmande nyckel:

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

Vi har skapat en främmande nyckel som heter fkey_student_admission på institutionstabellen. Denna främmande nyckel refererar till antagningskolumnen i elevtabellen.

Exempelfråga UTLÄNDSK NYCKEL

Låt oss först titta på våra överordnade tabelldata, COURSE.

Fråga:

SELECT * from COURSE;

SELECT-resultat som visar den överordnade tabellen COURSE-data

Nu ska vi infoga några rader i undertabellen 'Course_Strength_TSQL'. Vi ska försöka infoga två typer av rader:

  • Den första typen, för vilken Course_Id i undertabellen finns i Course_Id i överordnade tabellen, det vill säga Course_Id = 1 och 2.
  • Den andra typen, för vilken Course_Id i undertabellen inte finns i Course_Id i överordnade tabellen, det vill säga Course_Id = 5.

Fråga:

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

Infogar underrader, inklusive Course_ID 5 som inte har någon matchande förälder

Resultat: Låt oss köra frågan tillsammans för att se våra överordnade och underordnade tabeller.

Raderna med Course_ID 1 och 2 finns i tabellen Course_Strength. Course_ID 5 är dock ett undantag, eftersom det inte finns någon matchande rad i den överordnade tabellen.

Överordnade och underordnade tabeller jämfördes; Course_ID 5 bryter mot referensintegritet

Vanliga frågor

En primärnyckel identifierar unikt varje rad i sin egen tabell och kan inte vara NULL. En främmande nyckel refererar till den primärnyckeln från en annan tabell för att upprätthålla referensintegritet. primärnyckel kontra främmande nyckel Jämförelse förklarar varje skillnad.

Ja. En främmande nyckel kan referera till antingen en primärnyckel eller vilken kolumn som helst som har en UNIK begränsning i den överordnade tabellen. Den refererade kolumnen måste innehålla unika värden så att varje underordnad rad matchar exakt en överordnad rad.

VID BORTTAGNING tar CASCADE automatiskt bort matchande underraderna när deras överordnade rad tas bort, keeping tabellerna är konsekventa. Alternativen är SET NULL, vilket rensar den underordnade främmande nyckeln, och NO ACTION, vilket blockerar borttagningen.

Ja. En självrefererande främmande nyckel pekar på en primärnyckel i samma tabell, som modellerar hierarkier, till exempel en anställds rad som refererar till sin chef. För självreferenser rekommenderar SQL Server ON DELETE NO ACTION för att undvika kaskadcykler.

Ja, såvida inte kolumnen deklareras som NOT NULL. En NULL-främmande nyckel innebär att underraden ännu inte är länkad till någon överordnad rad, och SQL Server hoppar över referenskontrollen för det NULL-värdet.

Kör ALTER TABLE child_table DROP CONSTRAINT fkey_name. Du måste ange begränsningsnamnet, vilket du hittar i sys.foreign_keys. Dropping Den främmande nyckeln tar bort relationen men lämnar både tabellerna och deras data oförändrade.

Ja. GitHub Copilot kan skriva FOREIGN KEY-begränsningar inuti CREATE TABLE- eller ALTER TABLE-satser från en naturligt språkprompt och föreslå den överordnade tabellen och den refererade kolumnen. Granska alltid nycklarna, referensåtgärderna och datatyperna innan du kör skriptet.

AI- och maskininlärningsverktyg granskar exempeldata och frågemönster för att föreslå vilka kolumner som ska bli främmande nycklar, upptäcka saknade eller överblivna relationer och rekommendera lämpliga åtgärder vid borttagning. Utvecklaren granskar varje förslag innan det tillämpas.

Sammanfatta detta inlägg med: