Om du äger ett företag måste du ha stött på värdet och behovet av data i din verksamhet. Att ha möjlighet att lagra och manipulera databaser tillför mer värde till verksamheten.
Databaser är organiserade i en särskild konvention och låter dig strukturera data i samband, vilket tar oss till relationsdatabaser, som har anammats som en form av datahantering sedan 1970-talet. Och på dagens marknad är relationsdatabaser att föredra på grund av deras kapacitet vid manipulering av data.
Även om det finns många tillgängliga relationsdatabaser där ute, har MySQL tagit sig till den ledande, rankad som världens nummer två, enligt Statista, från och med januari 2022.
I SQL-servern är begränsningar fördefinierade regler och begränsningar som tillämpas i antingen en enstaka eller flera kolumner; de är knutna till värdena i kolumnen och hjälper till att upprätthålla integriteten, noggrannheten och tillförlitligheten för de angivna kolumnernas data.
Enkelt uttryckt, endast data som uppfyller begränsningsregeln infogas framgångsrikt i kolumnen. Infogningsoperationen avslutas om data inte uppfyller kriterierna.
Det här inlägget förutsätter att du har stött på relationsdatabaser, specifikt – MySQL, och ser fram emot att stärka dina kunskaper inom domänen. I slutändan kommer jag att dela med mig av några tips för att interagera med utländska nyckelbegränsningar.
Innehållsförteckning
Primära nyckelbegränsningar – en sammanfattning
En tabell i SQL innebär en kolumn eller flera som innehåller nyckelvärden som exakt pekar ut varje rad i systemen. Kolumnen eller kolumnerna med rubriken primärnyckel (PK) i tabellen har rollen att upprätthålla tabellens enhetsintegritet. Primära nyckelbegränsningar garanterar unik data och definieras ofta på en identitetskolumn.
När du anger primärnyckelbegränsningarna för din tabell, inför databasmotorn automatiskt dataunikhet genom att generera unika index för var och en av de primära kolumnerna. Primära nycklar erbjuder en extrem fördel när de används i frågor genom att ge snabb dataåtkomst.
Om primärnyckelbegränsningar definieras på flera kolumner, kallas det en sammansatt eller sammansatt primärnyckel. Och i det här fallet kan varje primärnyckelkolumn innehålla dubbletter av värden. De kombinerade värdena från alla kolumner i primärnyckeln måste dock vara unika.
Ett bra exempel är ett fall där du har en tabell med kolumnerna ”id”, ”namn” och ”ålder”. När du definierar dess primärnyckelbegränsning på kombinationen av `id` och `names`, kan du ha dubbletter av antingen `id`- eller `names`-värden. Ändå måste varje kombination vara unik för att undvika dubbletter av rader. Så du kan ha poster med ”id=1” och ”name=Walter”, och ”age-22” och ”id=1”, ”name=Henry” och ”age=27”, men du kan inte ha andra poster med `id=1` och `name=Walter` eftersom kombinationen inte är unik.
Här är några viktiga aspekter att känna till:
Utländska nyckelbegränsningar – en genomgång
En främmande nyckel (FK) innebär en kolumn eller en kombination av flera som används för att skapa och binda en länk mellan två tabeller och hanterar data som ska lagras i en främmande nyckeltabell.
En främmande nyckelreferens innebär att skapa en länk mellan två tabeller; när en kolumn eller kolumner som innehåller primärnyckeln för en annan tabell refereras av en eller flera kolumner i en annan tabell.
I referensscenariot för främmande nyckel skapas en koppling mellan två tabeller när en eller flera kolumner som innehåller primärnycklar i en tabell refereras av kolumner i en annan.
I ett praktiskt användningsfall kan du ha en tabell, Sales.SalesOrderHeader, med en främmande nyckel som länkar till en annan tabell, Sälj.Person, eftersom det finns ett logiskt förhållande mellan säljare och försäljningsorder.
Här blandas SalesPersonID i kolumnen SalesOrderHeader med primärnyckelkolumnen i SalesPerson-tabellen. SalesPerson-tabellens främmande nyckel är SalesPersonID-kolumnen i SalesOrderHeader.
Denna relation definierar en regel: ett SalesPersonID-värde kan inte finnas i din SalesOrderHeader-tabell om det inte finns i SalesPerson-tabellen.
En tabell kan referera till upp till 253 andra kolumner och tabeller som främmande nycklar, alternativt kallade utgående referenser. Sedan 2016 har SQL-servern höjt antalet tabeller och kolumner som du kan referera till i en enda tabell, även känd som inkommande referenser, från 253 till 10 000. Ökningen kommer dock med vissa begränsningar:
Vilka är fördelarna med främmande nycklar?
Som tidigare nämnts spelar främmande nyckelbegränsningar en viktig roll för att skydda integriteten och datakonsistensen i relationsdatabasen. Här är en uppdelning av skälen till varför utländska nyckelbegränsningar är viktiga.
Index av främmande nyckelbegränsningar
Utländska nyckelbegränsningar skapar inte automatiskt motsvarande index som det primära. Du kan manuellt skapa index för främmande nyckelbegränsningar; det är fördelaktigt av följande skäl.
- Kolumner för främmande nyckel används ofta i kopplingskriterierna när man kombinerar data från relaterade tabeller i frågor genom att matcha kolumnerna kopplade till begränsningen. Index hjälper databasen att hitta tillhörande data i en främmande tabell.
- Om du ändrar primärnyckelrestriktioner kontrolleras de med de främmande i relaterade tabeller.
Det är inte obligatoriskt att skapa index. Du kan fortfarande kombinera data från två tabeller utan att ange primär- och främmande nyckelbegränsningar. Att lägga till begränsningar för främmande nyckel optimerar dock tabellerna och kombinerar dem i en fråga som uppfyller kriterierna för att använda nycklarna. Om du ändrar primärnyckelrestriktioner kontrolleras de med de främmande i relaterade.
Tips för att skapa främmande nyckelbegränsningar i SQL
Du har redan lagt ner mycket tid på spekulationerna; svarade på varför. Låt oss flytta vårt fokus och begränsa det till taktiken för att skapa främmande nyckelbegränsningar; svara på hur.
Fältet ”Främmande nyckel” i en tabell hänvisar till en annans ”Primärnyckel”. Tabellen med primärnyckeln är din överordnade tabell. Och tabellen med den främmande nyckeln kallas barntabellen. Låt oss dyka in.
Skapa en främmande nyckel samtidigt som du skapar en tabell
När du skapar en tabell kan du också skapa en främmande nyckel-begränsning för att upprätthålla referensintegritet. Så här gör du:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Koden ovan skapar en tabell som heter ’order’ med den primära heltalsnyckeln ’order_id’, ett annat heltal ’kund-id’ och datumet ’orderdatum’. I det här fallet läggs FOREIGN KEY-begränsningen till i kolumnen ’customer_id’ och refererar till ’customer_id’ i din ’customers’-tabell.
Skapa en främmande nyckel efter att ha skapat en tabell
Anta att du redan har skapat en tabell och vill lägga till en främmande nyckel-begränsning; använd ”ALTER TABLE”-satsen i din kod. Titta på kodavsnittet nedan.
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
I det här fallet har du lagt till kolumnen ’customer_id’ för främmande nyckel, i tabellen ’order’ för att referera till kolumnen ’customer_id’ i tabellen ’customers’.
Skapa en främmande nyckel utan att söka efter befintliga data
När du lägger till en främmande nyckel-begränsning i en tabell, söker databasen automatiskt efter befintliga data för att säkerställa överensstämmelse med begränsningen. Men om du vet att data är konsekventa och vill lägga till en begränsning utan konsistenskontrollen, så här gör du det.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) NOT VALIDATE;
Kommandot NOT VALIDATE säger åt databasen att inte söka efter befintliga data. Det här fallet är användbart i specifika fall. Till exempel när du har massiva data och vill slutföra valideringsprocessen.
Skapa en främmande nyckel via DELETE/UPDATE
När du skapar begränsningar för främmande nyckel kan du styra den åtgärd som ska vidtas i fall där den refererade raden uppdateras eller tas bort. I det här fallet använder du kaskadande referensintegritetsbegränsningar för att diktera vilka åtgärder som ska vidtas. De inkluderar:
#1. INGEN ACTION
Som med många andra databaser är regeln ’INGEN ÅTGÄRD’ standardbeteendet när du skapar en främmande nyckel-begränsning. Det betyder att ingen åtgärd vidtas när den refererade raden raderas eller uppdateras.
Databasmotorn ger upphov till ett fel om den främmande nyckeln överträds. Detta rekommenderas dock inte eftersom det kan leda till referensintegritetsproblem eftersom begränsningen av den främmande nyckeln måste upprätthållas. Här är ett exempel på hur man gör:
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. KASKAD
Regeln ’CASCADE’ är ett annat alternativ för åtgärderna ’ON DELETE’ och ’ON UPDATE’ när du skapar begränsningar för främmande nyckel. När det är på plats betyder det att när en rad uppdateras eller tas bort i de överordnade tabellerna, uppdateras eller raderas de refererade raderna i enlighet med detta. Denna teknik är kraftfull när man upprätthåller referensintegritet. Här är ett exempel:
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 vara angelägen när du använder denna regel, eftersom den kan orsaka oönskade konsekvenser om den inte används försiktigt. Du vill undvika att av misstag radera för mycket data eller skapa cirkulära referenser. Använd därför endast detta alternativ om nödvändigt och med försiktighet.
Det finns några regler för att använda CASCADE:
- Du kan inte ange CASCADE om en tidsstämpelkolumn antingen är en del av den främmande eller refererade nyckeln.
- Om ditt bord har en utlösare I STÄLLET FÖR DELETE, kan du inte ange PÅ RADERAD CASCADE.
- Du kan inte ange ON UPDATE CASCADE om din tabell har utlösaren Istället FÖR UPPDATERING.
#3. SÄTT NULL
När du tar bort eller uppdaterar en motsvarande rad i den överordnade tabellen ställs alla värden som utgör den främmande nyckeln till null. Denna begränsningsregel kräver att kolumner för främmande nyckel ska vara nullbara att exekvera och kan inte specificeras för tabeller som har INSTEAD OF UPDATE-utlösare. Här är ett exempel på hur man gör.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL ON UPDATE SET NULL
I det här fallet har du ställt in den främmande nyckelkolumnen ’customer_id’ i tabellen ”order” till att vara null om motsvarande rad i tabellen ”kunder” tas bort eller uppdateras.
#4. SÄTTA SOM NORMALT
Här ställer du in alla värden som gör den främmande nyckeln som standard förutsatt att den refererade raden i den överordnade tabellen uppdateras eller tas bort.
Denna begränsning körs om alla kolumner för främmande nyckel har standarddefinitioner. Om en kolumn är nullbar är dess standardvärde satt till NULL. Observera att det här alternativet inte kan anges för tabeller med utlösare INSTEAD OF UPDATE. Här är ett exempel:
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 fallet ovan har du ställt in ’customer_id’ i tabellen ”order” till dess standardvärde, vilket händer när motsvarande rad i tabellen ”kunder” tas bort eller uppdateras.
Slutord
I den här guiden har du fått en uppdatering om primärnyckelbegränsningar och grävt i främmande nyckelbegränsningar. Du har också stött på flera tekniker för att skapa främmande nyckelbegränsningar. Och även om det finns många sätt att skapa främmande nyckelbegränsningar, har det här inlägget avslöjat metoderna.
Och hoppas att du har förstått nya tekniker; du är inte begränsad till att kombinera dem. Till exempel kan begränsningsmetoderna CASCADE, SET NULL, SET DEFAULT och NO ACTION kombineras på tabeller med referensrelationer.
Om din tabell stöter på NO ACTION, återgår den till andra restriktioner. I andra fall kan en DELETE-åtgärd utlösa en kombination av dessa regler, och regeln NO ACTION kommer att köras som den sista.
Kolla sedan in SQL-fuskbladet.