Främmande nyckel - Foreign key

En främmande nyckel är en uppsättning attribut i en tabell som hänvisar till huvudnyckeln i en annan tabell. Den utländska nyckeln länkar dessa två tabeller. Ett annat sätt att uttrycka det: I samband med relationsdatabaser är en främmande nyckel en uppsättning attribut som omfattas av en viss typ av inkluderingsberoendebegränsningar, särskilt en begränsning som tuplerna som består av de främmande nyckelattributen i en relation , R, måste finns också i någon annan (inte nödvändigtvis distinkt) relation, S, och dessutom att dessa attribut också måste vara en kandidatnyckel i S. Med enklare ord är en främmande nyckel en uppsättning attribut som refererar till en kandidatnyckel. Till exempel kan en tabell som heter TEAM ha ett attribut, MEMBER_NAME, som är en främmande nyckel som refererar till en kandidatnyckel, PERSON_NAME, i PERSON -tabellen. Eftersom MEMBER_NAME är en främmande nyckel måste alla värden som finns som namnet på en medlem i TEAM också finnas som en persons namn i PERSON -tabellen. med andra ord, varje medlem i ett TEAM är också en PERSON.

Sammanfattning

Tabellen som innehåller den främmande nyckeln kallas barntabellen och tabellen som innehåller kandidatnyckeln kallas referens- eller överordnadstabellen. I databasrelationsmodellering och implementering är en kandidatnyckel en uppsättning noll eller fler attribut, vars värden garanterat är unika för varje tupel (rad) i en relation. Värdet eller kombinationen av värden för kandidatnyckelattribut för valfri tupel kan inte kopieras för någon annan tupel i den relationen.

Eftersom syftet med den främmande nyckeln är att identifiera en viss rad med tabell som det refereras till krävs det i allmänhet att den främmande nyckeln är lika med kandidatnyckeln i någon rad i den primära tabellen, eller annars inte har något värde ( NULL -värdet.) . Denna regel kallas en referensiell integritetsbegränsning mellan de två tabellerna. Eftersom kränkningar av dessa begränsningar kan vara källan till många databasproblem, tillhandahåller de flesta databashanteringssystem mekanismer för att säkerställa att varje icke-null-utländsk nyckel motsvarar en rad i tabellen.

Tänk till exempel på en databas med två tabeller: en KUNDTABELL som innehåller all kunddata och en BESTÄLLNINGSTABELL som innehåller alla kundorder. Antag att verksamheten kräver att varje beställning måste hänvisa till en enda kund. För att återspegla detta i databasen läggs en utländsk nyckelkolumn till ORDER -tabellen (t.ex. CUSTOMERID), som refererar till KUNDENS primära nyckel (t.ex. ID). Eftersom en nyckel i en tabell måste vara unik och eftersom CUSTOMERID endast innehåller värden från det primära nyckelfältet kan vi anta att CUSTOMERID kommer att identifiera den specifika kund som lade ordern när den har ett värde. Detta kan dock inte längre antas om ORDER -tabellen inte hålls uppdaterad när rader i KUNDTABELLEN raderas eller ID -kolumnen ändras, och arbetet med dessa tabeller kan bli svårare. Många verkliga databaser löser detta problem genom att 'inaktivera' snarare än att fysiskt radera huvudtabellens främmande nycklar eller genom komplexa uppdateringsprogram som ändrar alla referenser till en främmande nyckel när en förändring behövs.

Utländska nycklar spelar en viktig roll i databasdesign . En viktig del av databasdesign är att se till att relationer mellan verkliga enheter återspeglas i databasen genom referenser, med hjälp av främmande nycklar för att hänvisa från en tabell till en annan. En annan viktig del av databasdesign är databasnormalisering , där tabeller bryts isär och främmande nycklar gör det möjligt att rekonstruera dem.

Flera rader i tabellen med referens (eller underordnad) kan hänvisa till samma rad i tabellen som refereras (eller överordnad). I det här fallet kallas förhållandet mellan de två tabellerna ett till många -förhållande mellan referensbordet och tabellen som refereras.

Dessutom kan tabellen för barn och förälder faktiskt vara samma tabell, det vill säga den främmande nyckeln refererar tillbaka till samma tabell. En sådan främmande nyckel är känd i SQL: 2003 som en självrefererande eller rekursiv främmande nyckel. I databashanteringssystem uppnås detta ofta genom att länka en första och andra referens till samma tabell.

En tabell kan ha flera utländska nycklar, och varje utländsk nyckel kan ha en annan överordnad tabell. Varje utländsk nyckel verkställs oberoende av databassystemet . Därför kan kaskadrelationer mellan tabeller upprättas med hjälp av främmande nycklar.

En främmande nyckel definieras som ett attribut eller en uppsättning attribut i en relation vars värden matchar en primär nyckel i en annan relation. Syntaxen för att lägga till en sådan begränsning i en befintlig tabell definieras i SQL: 2003 enligt nedan. Att utelämna kolumnlistan i REFERENCESklausulen innebär att den främmande nyckeln ska referera till den primära nyckeln i den refererade tabellen. På samma sätt kan främmande nycklar definieras som en del av CREATE TABLESQL -satsen. 

CREATE TABLE child_table (
  col1 INTEGER PRIMARY KEY,
  col2 CHARACTER VARYING(20),
  col3 INTEGER,
  col4 INTEGER,
  FOREIGN KEY(col3, col4) REFERENCES parent_table(col1, col2) ON DELETE CASCADE
)

Om den främmande nyckeln bara är en enda kolumn kan kolumnen markeras som sådan med följande syntax: 

CREATE TABLE child_table (
  col1 INTEGER PRIMARY KEY,
  col2 CHARACTER VARYING(20),
  col3 INTEGER,
  col4 INTEGER REFERENCES parent_table(col1) ON DELETE CASCADE
)

Utländska nycklar kan definieras med ett lagrat proceduruttalande . 

sp_foreignkey child_table, parent_table, col3, col4
  • child_table : namnet på tabellen eller vyn som innehåller den främmande nyckeln som ska definieras.
  • parent_table : namnet på tabellen eller vyn som har den primära nyckel som den främmande nyckeln gäller. Den primära nyckeln måste redan definieras.
  • col3 och col4 : namnet på kolumnerna som utgör den främmande nyckeln. Den främmande nyckeln måste ha minst en kolumn och högst åtta kolumner.

Referensåtgärder

Huvudartikel: Förökningsbegränsning

Eftersom databashanteringssystemet tillämpar referensbegränsningar måste det säkerställa dataintegritet om rader i en refererad tabell ska raderas (eller uppdateras). Om beroende rader i hänvisningstabeller fortfarande finns måste dessa referenser beaktas. SQL: 2003 anger 5 olika referensåtgärder som ska ske vid sådana händelser:

KASKAD

När rader i den överordnade (refererade) tabellen raderas (eller uppdateras), kommer respektive rader i underordnadstabellen (referens) med en matchande utländsk nyckelkolumn att raderas (eller uppdateras) också. Detta kallas en kaskadradering (eller uppdatering).

BEGRÄNSA

Ett värde kan inte uppdateras eller tas bort när det finns en rad i en referens- eller underordnad tabell som refererar till värdet i den refererade tabellen.

På samma sätt kan en rad inte raderas så länge det finns en referens till den från en referens- eller underordnad tabell.

För att förstå RESTRICT (och CASCADE) bättre kan det vara till hjälp att lägga märke till följande skillnad, vilket kanske inte är klart direkt. Referensåtgärden CASCADE ändrar "beteendet" i själva (under) tabellen där ordet CASCADE används. Till exempel säger ON DELETE CASCADE effektivt "När den refererade raden raderas från den andra tabellen (huvudtabellen), ta bort den också från mig ". Referensåtgärden RESTRICT ändrar emellertid "beteendet" för mastertabellen, inte barnbordet, även om ordet RESTRICT förekommer i barnbordet och inte i huvudtabellen! Så, ON DELETE RESTRICT säger effektivt: "När någon försöker radera raden från den andra tabellen (huvudtabellen), förhindra borttagning från den andra tabellen (och naturligtvis inte heller ta bort från mig, men det är inte huvudpoängen här)."

RESTRICT stöds inte av Microsoft SQL 2012 och tidigare.

INGEN ACTION

INGEN HANDLING och BEGRÄNSNING är väldigt lika. Huvudskillnaden mellan NO ACTION och RESTRICT är att med NO ACTION utförs referenskontrollen efter att ha försökt ändra tabellen. RESTRICT gör kontrollen innan du försöker köra UPDATE eller DELETE -satsen. Båda referensåtgärderna fungerar likadant om kontrollen av referensintegritet misslyckas: UPDATE- eller DELETE -satsen kommer att resultera i ett fel.

Med andra ord, när en UPDATE- eller DELETE -sats körs på den refererade tabellen med hjälp av referensåtgärden NO ACTION, verifierar DBMS vid slutet av statement -körningen att ingen av referensrelationerna bryts. Detta skiljer sig från RESTRICT, som från början antar att operationen kommer att bryta mot begränsningen. Genom att använda INGEN ÅTGÄRD kan utlösarna eller semantiken i själva uttalandet ge ett slutläge där inga främmande nyckelrelationer kränks när begränsningen slutligen kontrolleras, vilket gör att påståendet kan slutföras framgångsrikt.

SET NULL, SET DEFAULT

I allmänhet är åtgärden som vidtas av DBMS för SET NULL eller SET DEFAULT densamma för både ON DELETE eller ON UPDATE: värdet för de berörda referensattributen ändras till NULL för SET NULL och till det angivna standardvärdet för SET DEFAULT .

Utlösare

Referensåtgärder implementeras i allmänhet som underförstådda utlösare (dvs. utlösare med systemgenererade namn, ofta dolda.) Som sådana är de föremål för samma begränsningar som användardefinierade utlösare, och deras ordningsföljd i förhållande till andra utlösare kan behöva vara anses vara; i vissa fall kan det bli nödvändigt att ersätta referensåtgärden med sin motsvarande användardefinierade utlösare för att säkerställa korrekt utförandeorder, eller för att kringgå mutationstabellbegränsningar.

En annan viktig begränsning visas med transaktionsisolering : dina ändringar i en rad kanske inte kan kaskad helt eftersom raden refereras till av data som din transaktion inte kan "se", och därför inte kan kaskad till. Ett exempel: medan din transaktion försöker ändra ett kundkonto, försöker en samtidig transaktion skapa en ny faktura för samma kund. medan en CASCADE -regel kan fixa alla fakturarader som din transaktion kan se för att hålla dem förenliga med den omnumrerade kundraden, kommer den inte att nå en annan transaktion för att fixa data där; eftersom databasen inte kan garantera konsekventa data när de två transaktionerna begår, kommer en av dem att tvingas rulla tillbaka (ofta enligt först till kvarn-principen.) 

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

CREATE TRIGGER ins_sum BEFORE INSERT ON account
     FOR EACH ROW SET @sum = @sum + NEW.amount;

Exempel

Som ett första exempel för att illustrera utländska nycklar, antar att en kontodatabas har en tabell med fakturor och varje faktura är associerad med en viss leverantör. Leverantörsinformation (t.ex. namn och adress) sparas i en separat tabell; varje leverantör får ett 'leverantörsnummer' för att identifiera det. Varje fakturapost har ett attribut som innehåller leverantörsnumret för fakturan. "Leverantörsnumret" är sedan huvudnyckeln i tabellen Leverantör. Den främmande nyckeln i fakturatabellen pekar på den primära nyckeln. Det relationella schemat är följande. Primära nycklar är markerade med fet stil, och utländska nycklar är markerade med kursiv. 

 Supplier (SupplierNumber, Name, Address)
 Invoice (InvoiceNumber, Text, SupplierNumber)

Motsvarande Data Definition Language -sats är följande. 

CREATE TABLE Supplier (
  SupplierNumber INTEGER NOT NULL,
  Name           VARCHAR(20) NOT NULL,
  Address        VARCHAR(50) NOT NULL,
  CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber),
  CONSTRAINT number_value CHECK(SupplierNumber > 0)
)

CREATE TABLE Invoice (
  InvoiceNumber  INTEGER NOT NULL,
  Text           VARCHAR(4096),
  SupplierNumber INTEGER NOT NULL,
  CONSTRAINT invoice_pk PRIMARY KEY(InvoiceNumber),
  CONSTRAINT inumber_value CHECK (InvoiceNumber > 0),
  CONSTRAINT supplier_fk
    FOREIGN KEY(SupplierNumber) REFERENCES Supplier(SupplierNumber)
    ON UPDATE CASCADE ON DELETE RESTRICT
)

Se även

Referenser

externa länkar