Dela via

Primär- och externnyckelbegränsningar

  • Artikel

gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Primära nycklar och sekundärnycklar är två typer av begränsningar som kan användas för att framtvinga dataintegritet i SQL Server-tabeller. Det här är viktiga databasobjekt.

Begränsningar för primärnyckel

En tabell har vanligtvis en kolumn eller kombination av kolumner som innehåller värden som unikt identifierar varje rad i tabellen. Den här kolumnen, eller kolumnerna, kallas primärnyckeln (PK) i tabellen och framtvingar tabellens entitetsintegritet. Eftersom primära nyckelbegränsningar garanterar unika data definieras de ofta i en identitetskolumn.

När du anger en primärnyckelbegränsning för en tabell framtvingar databasmotorn data unikhet genom att automatiskt skapa ett unikt index för primärnyckelkolumnerna. Det här indexet tillåter också snabb åtkomst till data när primärnyckeln används i frågor. Om en primärnyckelvillkor har definierats för mer än en kolumn kan värden dupliceras inom en kolumn, men varje kombination av värden från alla kolumner i definitionen av primärnyckelvillkor måste vara unik.

Som du ser i följande bild utgör kolumnerna ProductID och VendorID i tabellen Purchasing.ProductVendor en sammansatt primärnyckelbegränsning för den här tabellen. Detta säkerställer att varje rad i tabellen ProductVendor har en unik kombination av ProductID och VendorID. Detta förhindrar att dubbletter av rader infogas.

Diagram över rader i en tabell för en sammansatt PRIMARY KEY-begränsning.

  • En tabell kan bara innehålla en primärnyckelbegränsning.
  • En primärnyckel får inte överskrida 16 kolumner och en total nyckellängd på 900 byte.
  • Indexet som genereras av en primärnyckelbegränsning kan inte leda till att antalet index i tabellen överskrider 999 icke-grupperade index och 1 klustrat index.
  • Om klustrad eller icke-klustrad inte har angetts för en primärnyckelbegränsning används klustrad om det inte finns något klustrat index i tabellen.
  • Alla kolumner som definieras inom en primärnyckelbegränsning måste definieras som inte är null. Om nullvärde inte är specificerat, så är alla kolumner som deltar i en primärnyckelbegränsning inställda på att inte tillåta null-värden.
  • Om en primärnyckel definieras i en CLR-användardefinierad typkolumn måste implementeringen av typen ha stöd för binär ordning.

Begränsningar för främmande nyckel

En sekundärnyckel (FK) är en kolumn eller kombination av kolumner som används för att upprätta och framtvinga en länk mellan data i två tabeller för att styra de data som kan lagras i sekundärnyckeltabellen. I en referens för sekundärnyckel skapas en länk mellan två tabeller när kolumnen eller kolumnerna som innehåller primärnyckelvärdet för en tabell refereras av kolumnen eller kolumnerna i en annan tabell. Den här kolumnen blir en främmande nyckel i den andra tabellen.

Till exempel har tabellen Sales.SalesOrderHeader en sekundärnyckellänk till tabellen Sales.SalesPerson eftersom det finns en logisk relation mellan försäljningsorder och säljare. Kolumnen SalesPersonID i tabellen SalesOrderHeader matchar den primära nyckelkolumnen i tabellen SalesPerson. Kolumnen SalesPersonID i tabellen SalesOrderHeader är sekundärnyckeln till tabellen SalesPerson. Genom att skapa den här sekundärnyckelrelationen kan ett värde för SalesPersonID inte infogas i tabellen SalesOrderHeader om den inte redan finns i tabellen SalesPerson.

En tabell kan referera till högst 253 andra tabeller och kolumner som sekundärnycklar (utgående referenser). SQL Server 2016 (13.x) ökar gränsen för antalet andra tabeller och kolumner som kan referera till kolumner i en enskild tabell (inkommande referenser), från 253 till 10 000. (Kräver minst 130 kompatibilitetsnivå.) Ökningen har följande begränsningar:

  • Mer än 253 referenser för utländsk nyckel stöds endast för DELETE DML-åtgärder. UPDATE och MERGE åtgärder stöds inte.

  • En tabell med en sekundärnyckelreferens till sig själv är fortfarande begränsad till 253 sekundärnyckelreferenser.

  • Fler än 253 sekundärnyckelreferenser är för närvarande inte tillgängliga för kolumnlagringsindex, minnesoptimerade tabeller, Stretch Database eller partitionerade sekundärnyckeltabeller.

    Viktig

    Stretch Database är inaktuell i SQL Server 2022 (16.x) och Azure SQL Database. Den här funktionen tas bort i en framtida version av databasmotorn. Undvik att använda den här funktionen i nytt utvecklingsarbete och planera att ändra program som för närvarande använder den här funktionen.

Index på externa nyckelbegränsningar

Till skillnad från primärnyckelbegränsningar skapar inte en referensnyckelbe-gränsning automatiskt ett motsvarande index. Att manuellt skapa ett index på en främmande nyckel är dock ofta användbart av följande skäl:

  • Sekundärnyckelkolumner används ofta i kopplingsvillkor när data från relaterade tabeller kombineras i frågor genom att matcha kolumnen eller kolumnerna i villkoret sekundärnyckel i en tabell med den primära eller unika nyckelkolumnen eller kolumnerna i den andra tabellen. Med ett index kan databasmotorn snabbt hitta relaterade data i sekundärnyckeltabellen. Det krävs dock inte att du skapar det här indexet. Data från två relaterade tabeller kan kombineras även om inga primärnyckel- eller sekundärnyckelbegränsningar definieras mellan tabellerna, men en sekundärnyckelrelation mellan två tabeller indikerar att de två tabellerna har optimerats för att kombineras i en fråga som använder nycklarna som villkor.

  • Ändringar av primärnyckelbegränsningar kontrolleras med begränsningar för främmandenyckel i relaterade tabeller.

Referensintegritet

Även om huvudsyftet med en utländsk nyckelbegränsning är att reglera de data som kan lagras i den utländska nyckeltabellen, reglerar den även ändringar i data i primärnyckeltabellen. Om raden för en säljare till exempel tas bort från tabellen Sales.SalesPerson och säljarens ID används för försäljningsorder i tabellen Sales.SalesOrderHeader bryts relationsintegriteten mellan de två tabellerna. den borttagna säljarens försäljningsorder överblivs i tabellen SalesOrderHeader utan en länk till data i tabellen SalesPerson.

En främmande nyckelbegränsning förhindrar den här situationen. Villkoret framtvingar referensintegritet genom att garantera att ändringar inte kan göras i data i primärnyckeltabellen om ändringarna ogiltigförklarar länken till data i tabellen med sekundärnyckel. Om ett försök görs att ta bort raden i en primärnyckeltabell eller ändra ett primärnyckelvärde misslyckas åtgärden när det borttagna eller ändrade primärnyckelvärdet motsvarar ett värde i villkoret sekundärnyckel i en annan tabell. Om du vill ändra eller ta bort en rad i en sekundärnyckelvillkor måste du först antingen ta bort sekundärnyckeldata i sekundärnyckeltabellen eller ändra sekundärnyckeldata i sekundärnyckeltabellen, som länkar sekundärnyckeln till olika primärnyckeldata.

Sammanhängande referensintegritet

Genom att använda sammanhängande begränsningar för referensintegritet kan du definiera de åtgärder som databasmotorn vidtar när en användare försöker ta bort eller uppdatera en nyckel som befintliga sekundärnycklar pekar på. Följande sammanhängande åtgärder kan definieras.

  • NO ACTION

    Databasmotorn genererar ett fel och åtgärden ta bort eller uppdatera på raden i den överordnade tabellen återställs.

  • CASCADE

    Motsvarande rader uppdateras eller tas bort i referenstabellen när den raden uppdateras eller tas bort i den överordnade tabellen. CASCADE kan inte anges om en tidsstämpelkolumn är en del av antingen den främmande nyckeln eller den refererade nyckeln. ON DELETE CASCADE kan inte anges för en tabell som har en INSTEAD OF DELETE utlösare. ON UPDATE CASCADE kan inte anges för tabeller som har INSTEAD OF UPDATE utlösare.

  • SET NULL

    Alla värden som utgör sekundärnyckeln anges till NULL när motsvarande rad i den överordnade tabellen uppdateras eller tas bort. För att den här begränsningen ska kunna köras måste främmande nyckelkolumnerna vara nullbara. Det går inte att ange för tabeller som har INSTEAD OF UPDATE utlösare.

  • SET DEFAULT

    Alla värden som utgör sekundärnyckeln anges till deras standardvärden om motsvarande rad i den överordnade tabellen uppdateras eller tas bort. För att den här begränsningen ska kunna köras måste alla främmande nyckelkolumner ha standarddefinitioner. Om en kolumn är nullbar och det inte finns någon explicit standardvärdeuppsättning blir NULL det implicita standardvärdet för kolumnen. Det kan inte specificeras för tabeller som har INSTEAD OF UPDATE triggers.

CASCADE, SET NULL, SET DEFAULToch NO ACTION kan kombineras i tabeller som har referensrelationer med varandra. Om databasmotorn stöter på NO ACTION, så stoppas och återställs relaterade åtgärder för CASCADE, SET NULLoch SET DEFAULT. När en DELETE-instruktion orsakar en kombination av CASCADE, SET NULL, SET DEFAULTeller NO ACTION åtgärder tillämpas alla CASCADE, SET NULLoch SET DEFAULT innan databasmotorn utför någon kontroll för NO ACTION.

Utlösare och sammanhängande referensåtgärder

Sammanhängande referensåtgärder utlöser AFTER UPDATE eller AFTER DELETE utlösare på följande sätt:

  • Alla sammanhängande referensåtgärder som orsakas direkt av den ursprungliga DELETE eller UPDATE utförs först.

  • Om det finns några AFTER utlösare som definierats i de berörda tabellerna utlöses dessa när alla sammanhängande åtgärder har utförts. Dessa utlösare utlöses i motsatt ordning i kaskadåtgärden. Om det finns flera utlösare i en enda tabell utlöses de i slumpmässig ordning, såvida det inte finns en dedikerad första eller sista utlösare för tabellen. Den här ordningen anges med hjälp av sp_settriggerorder.

  • Om flera kaskadkedjor kommer från tabellen som var direkt mål för en UPDATE eller DELETE åtgärd, är ordningen i vilken dessa kedjor aktiverar sina respektive utlösare ospecificerad. Men en kedja aktiverar alltid alla sina triggers innan en annan kedja börjar aktiveras.

  • En AFTER-trigger i tabellen som är det direkta målet för en UPDATE eller DELETE åtgärd aktiveras oavsett om några rader påverkas. Det finns inga andra tabeller som påverkas av kaskad i det här fallet.

  • Om någon av de tidigare utlösarna utför UPDATE eller DELETE åtgärder i andra tabeller kan dessa åtgärder starta sekundära sammanhängande kedjor. Dessa sekundära kedjor bearbetas för varje enskild UPDATE eller DELETE åtgärd åt gången, efter att alla utlösare på alla primära kedjor har utlösts. Den här processen kan upprepas rekursivt för efterföljande UPDATE eller DELETE åtgärder.

  • Om du utför CREATE, ALTER, DELETEeller andra DDL-åtgärder (datadefinitionsspråk) i utlösarna kan DDL-utlösare utlösas. Detta kan senare utföra ta bort- eller uppdatera-åtgärder som startar ytterligare kaskadkedjor och triggers.

  • Om ett fel genereras i en viss sammanhängande referensåtgärdskedja utlöses ett fel, inga AFTER utlösare utlöses i den kedjan och åtgärden DELETE eller UPDATE som skapade kedjan återställs.

  • En tabell som har en INSTEAD OF utlösare kan inte heller ha en REFERENCES-sats som anger en sammanhängande åtgärd. En AFTER-utlösare på en tabell som är mål för en kaskadåtgärd kan dock köra en INSERT-, UPDATE- eller DELETE-instruktion på en annan tabell eller vy som utlöser en INSTEAD OF-utlösare definierad för det objektet.

Relaterat innehåll