För företagare är det oundvikligt att stöta på vikten av data. Möjligheten att lagra och hantera databaser ger en stor konkurrensfördel.
Databaser är systematiskt organiserade för att strukturera information i sammanhang. Relationsdatabaser har varit en populär metod för datahantering sedan 1970-talet. Idag är de ett förstahandsval tack vare sin flexibilitet i datahantering.
Även om det finns många relationsdatabaser att välja mellan, har MySQL intagit en ledande ställning. Enligt Statista rankades den som nummer två i världen i januari 2022.
I SQL-servrar används begränsningar som fördefinierade regler för enskilda eller flera kolumner. Dessa är kopplade till kolumnvärden och bidrar till att upprätthålla integriteten, noggrannheten och tillförlitligheten hos datan.
Enkelt uttryckt, endast data som överensstämmer med dessa begränsningar kan läggas till i en kolumn. Infogningen avbryts om kriterierna inte uppfylls.
Detta inlägg förutsätter att du är bekant med relationsdatabaser, särskilt MySQL, och vill utöka dina kunskaper inom området. Vi kommer också att ge dig tips om hur du interagerar med främmande nyckelbegränsningar.
Primärnyckelbegränsningar – en översikt
En tabell i SQL innehåller en eller flera kolumner med nyckelvärden som unikt identifierar varje rad. Kolumnen eller kolumnerna som definieras som primärnyckel (PK) har i uppgift att upprätthålla tabellens integritet. Primärnyckelbegränsningar garanterar att datan är unik och definieras ofta på en identitetskolumn.
När du definierar primärnyckelbegränsningar för din tabell skapar databasmotorn automatiskt unika index för de primära kolumnerna för att säkerställa att datan är unik. Primärnycklar ger snabb dataåtkomst vid användning i sökfrågor.
Om primärnyckelbegränsningar definieras på flera kolumner kallas det en sammansatt primärnyckel. Varje enskild kolumn i en sammansatt primärnyckel kan innehålla dubbletter av värden, men den kombinerade uppsättningen av värden måste vara unik.
Ett exempel är en tabell med kolumnerna ”id”, ”namn” och ”ålder”. Om primärnyckelbegränsningen definieras på kombinationen av `id` och `namn`, kan du ha dubbletter av antingen `id` eller `namn`, men kombinationen måste vara unik. Du kan ha poster som ”id=1” och ”namn=Walter” med ”ålder=22” samt ”id=1”, ”namn=Henry” och ”ålder=27”. Det går dock inte att ha en annan post med `id=1` och `namn=Walter` eftersom den kombinationen redan finns.
Viktigt att veta:
- En tabell kan bara ha en primärnyckelbegränsning.
- Primärnycklar får inte innehålla mer än 16 kolumner och får inte överskrida en maximal längd på 900 tecken.
- Index som skapas av primärnycklar kan öka index i tabellen. Det finns en gräns på ett klustrat index per tabell, medan antalet icke-klustrade index är begränsat till 999.
- Om klustrade och icke-klustrade index inte anges, antas klustrade automatiskt.
- Alla kolumner som ingår i en primärnyckelbegränsning måste definieras som ”not null”. Om de inte är det, kommer nullbarheten automatiskt att ställas in på ”not null”.
- När primärnycklar definieras för användardefinierade kolumntyper (CLR), måste typimplementeringen stödja binär ordning.
Främmande nyckelbegränsningar – en genomgång
En främmande nyckel (FK) utgör en kolumn eller en kombination av flera kolumner som används för att skapa och underhålla en länk mellan två tabeller. Den hanterar data som lagras i en främmande nyckeltabell.
En främmande nyckel referens sker när en länk skapas mellan två tabeller. Detta innebär att en eller flera kolumner i en tabell refererar till primärnyckeln i en annan tabell.
I praktiken skapas en koppling mellan två tabeller när en eller flera kolumner i en tabell refererar till primärnycklarna i en annan tabell.
Ett praktiskt exempel är tabellen `Sales.SalesOrderHeader` med en främmande nyckel som refererar till tabellen `Sales.Person`. Detta illustrerar den logiska relationen mellan säljare och försäljningsordrar.
I det här fallet kopplas `SalesPersonID` i `SalesOrderHeader` till primärnyckelkolumnen i tabellen `SalesPerson`. Den främmande nyckeln i tabellen `SalesPerson` är alltså kolumnen `SalesPersonID` i `SalesOrderHeader`.
Denna relation fastställer en regel: Ett värde i `SalesPersonID` kan inte finnas i `SalesOrderHeader`-tabellen om det inte också finns i `SalesPerson`-tabellen.
En tabell kan referera till upp till 253 andra kolumner och tabeller som främmande nycklar, så kallade utgående referenser. Sedan 2016 har SQL-servern ökat antalet tabeller och kolumner som kan refereras i en enskild tabell, även kallat inkommande referenser, från 253 till 10 000. Men denna ökning kommer med vissa begränsningar:
- Utländska nyckelreferenser som överskrider 253 är endast tillgängliga för `DELETE` DML-operationer. `MERGE` och `UPDATE` stöds inte.
- Tabeller med främmande nyckelreferenser till sig själva är begränsade till maximalt 253 främmande nyckelreferenser.
- För kolumnlagerindex, minnesoptimerade tabeller och partitionerade främmande nyckeltabeller är referenser för främmande nyckel begränsade till 253.
Vilka är fördelarna med främmande nycklar?
Som tidigare nämnts är främmande nyckelbegränsningar viktiga för att skydda integriteten och datakonsistensen i en relationsdatabas. Nedan följer en genomgång av varför främmande nyckelbegränsningar är så viktiga.
- Referensintegritet – Främmande nyckelbegränsningar säkerställer att varje post i en underordnad tabell motsvarar en post i en överordnad tabell. Detta säkerställer datakonsistens i båda tabellerna.
- Förhindra övergivna poster – Om en post tas bort från en överordnad tabell, säkerställer främmande nyckelbegränsningar att den tillhörande posten i den underordnade tabellen också tas bort. Detta förhindrar övergivna poster som kan leda till inkonsekvens i datan.
- Förbättrad prestanda – Främmande nyckelbegränsningar ökar frågeprestandan genom att databashanteringssystemet kan optimera sökningar baserat på relationerna mellan tabellerna.
Index för främmande nyckelbegränsningar
Främmande nyckelbegränsningar skapar inte automatiskt motsvarande index, till skillnad från primära nycklar. Index kan skapas manuellt för främmande nyckelbegränsningar och detta är fördelaktigt av följande skäl:
- Kolumner med främmande nycklar används ofta i kopplingsvillkor för att kombinera data från relaterade tabeller. Index hjälper databasen att hitta tillhörande data snabbare.
- Om du ändrar primärnyckelbegränsningar, kontrolleras det med främmande nyckelbegränsningar i de relaterade tabellerna.
Det är inte obligatoriskt att skapa index. Data från två tabeller kan kombineras även utan primär- och främmande nyckelbegränsningar. Genom att lägga till begränsningar för främmande nycklar optimeras emellertid tabellerna och sökningar som använder nycklarna. Om primärnyckelbegränsningar ändras kontrolleras det med främmande nyckelbegränsningar i de relaterade tabellerna.
Tips för att skapa främmande nyckelbegränsningar i SQL
Vi har gått igenom teorin. Nu går vi in på praktiken och fokuserar på hur man skapar främmande nyckelbegränsningar.
Ett fält med en främmande nyckel refererar till en annans primärnyckel. Tabellen med primärnyckeln är den överordnade tabellen, och tabellen med den främmande nyckeln kallas den underordnade tabellen.
Skapa en främmande nyckel när du skapar en tabell
När du skapar en tabell kan du samtidigt skapa en främmande nyckelbegränsning för att upprätthålla referensintegritet. Här är hur du gör:
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 `orders` med en primärnyckel `order_id`, en `customer_id` samt `order_date`. I detta fall läggs `FOREIGN KEY`-begränsningen till i kolumnen `customer_id` och refererar till `customer_id` i tabellen `customers`.
Skapa en främmande nyckel efter att ha skapat en tabell
Om du redan har skapat en tabell och vill lägga till en främmande nyckelbegränsning, använder du `ALTER TABLE`-satsen i din kod. Här är ett exempel:
ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
I detta exempel läggs kolumnen `customer_id` till i tabellen `orders` som en främmande nyckel. Den refererar till kolumnen `customer_id` i tabellen `customers`.
Skapa en främmande nyckel utan att kontrollera befintlig data
När en främmande nyckelbegränsning läggs till, söker databasen automatiskt efter befintlig data för att säkerställa överensstämmelse. Men om du vet att datan är konsekvent kan du lägga till en begränsning utan konsistenskontroll, så här gör du:
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
NOT VALIDATE;
Kommandot `NOT VALIDATE` talar om för databasen att inte söka efter befintlig data. Detta kan vara användbart i vissa situationer, till exempel när du hanterar stora datamängder och vill undvika en omfattande validering.
Skapa en främmande nyckel via DELETE/UPDATE
När du skapar främmande nyckelbegränsningar kan du styra hur systemet ska hantera händelser när den refererade raden uppdateras eller tas bort. I detta fall används referensintegritetsbegränsningar för att definiera vilka åtgärder som ska vidtas. Dessa inkluderar:
#1. INGEN ÅTGÄRD
Som standard i många databaser, är beteendet `NO ACTION` när du skapar en främmande nyckelbegränsning. Det innebär att ingen åtgärd vidtas när den refererade raden raderas eller uppdateras.
Databasmotorn genererar ett fel om den främmande nyckeln överskrids. Detta rekommenderas inte eftersom det kan orsaka problem med referensintegriteten, som ska upprätthållas av begränsningen. 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 `ON DELETE` och `ON UPDATE` när du skapar främmande nyckelbegränsningar. När denna är på plats, betyder det att när en rad uppdateras eller raderas i en överordnad tabell, uppdateras eller raderas de refererade raderna i motsvarande tabell. Detta kan vara användbart när du vill säkerställa 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;
Var försiktig när du använder denna regel eftersom det kan leda till oönskade konsekvenser om den inte hanteras med omsorg. Det kan finnas risk att av misstag radera för mycket data eller skapa cirkulära referenser. Använd därför detta alternativ med försiktighet och endast vid behov.
Det finns vissa regler för att använda `CASCADE`:
- Du kan inte ange `CASCADE` om en tidskolumn är en del av den främmande eller refererade nyckeln.
- Om din tabell har en `INSTEAD OF DELETE`-utlösare, kan du inte ange `ON DELETE CASCADE`.
- Du kan inte ange `ON UPDATE CASCADE` om din tabell har en `INSTEAD OF UPDATE`-utlösare.
#3. SÄTT NULL
När motsvarande rad tas bort eller uppdateras i den överordnade tabellen sätts alla värden som utgör den främmande nyckeln till null. Denna begränsningsregel kräver att kolumner med främmande nycklar ska vara nullbara. Den kan inte anges 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 detta fall har du ställt in kolumnen `customer_id` i tabellen `orders` till null om motsvarande rad i tabellen `customers` tas bort eller uppdateras.
#4. SÄTT SOM STANDARD
Med det här alternativet ställer du in värdena för alla kolumner i den främmande nyckeln till ett standardvärde om den refererade raden i den överordnade tabellen uppdateras eller tas bort.
Denna begränsning tillämpas endast om alla kolumner med främmande nycklar har en standarddefinition. Om en kolumn är nullbar sätts standardvärdet till `NULL`. Observera att det här alternativet inte kan anges för tabeller som har `INSTEAD OF UPDATE`-utlösare. 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 exemplet ovan har `customer_id` i tabellen `orders` satts till standardvärdet när motsvarande rad i tabellen `customers` tas bort eller uppdateras.
Sammanfattning
Den här guiden har gett dig en genomgång av primärnyckelbegränsningar och djupdykt i främmande nyckelbegränsningar. Vi har även gått igenom flera metoder för att skapa främmande nyckelbegränsningar. Även om det finns många metoder, har detta inlägg fokuserat på de vanligaste.
Vi hoppas att du har lärt dig nya tekniker, men kom ihåg att du inte är begränsad till att använda dem separat. Du kan till exempel kombinera begränsningsmetoderna `CASCADE`, `SET NULL`, `SET DEFAULT` och `NO ACTION` på tabeller med referensrelationer.
Om en tabell stöter på `NO ACTION`, går den tillbaka till andra begränsningar. Vid andra tillfällen kan en `DELETE`-åtgärd utlösa en kombination av dessa regler, och `NO ACTION` kommer att utföras sist.
Kolla även in SQL-fusklappen.