Delen via

Beperkingen voor primaire en vreemde sleutels

  • Artikel

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Primaire sleutels en refererende sleutels zijn twee typen beperkingen die kunnen worden gebruikt voor het afdwingen van gegevensintegriteit in SQL Server-tabellen. Dit zijn belangrijke databaseobjecten.

Beperkingen voor primaire sleutels

Een tabel heeft doorgaans een kolom of combinatie van kolommen die waarden bevatten die elke rij in de tabel uniek identificeren. Deze kolom of kolommen wordt de primaire sleutel (PK) van de tabel genoemd en dwingt de entiteitsintegriteit van de tabel af. Omdat primaire-sleutelbeperkingen unieke gegevens garanderen, worden ze vaak gedefinieerd in een identiteitskolom.

Wanneer u een primaire-sleutelbeperking voor een tabel opgeeft, dwingt de database-engine de uniekheid van gegevens af door automatisch een unieke index te maken voor de primaire-sleutelkolommen. Deze index biedt ook snelle toegang tot gegevens wanneer de primaire sleutel wordt gebruikt in query's. Als een primaire-sleutelbeperking is gedefinieerd voor meer dan één kolom, kunnen waarden in één kolom worden gedupliceerd, maar elke combinatie van waarden uit alle kolommen in de definitie van de primaire sleutelbeperking moet uniek zijn.

Zoals in de volgende afbeelding wordt weergegeven, vormen de kolommen ProductID en VendorID in de Purchasing.ProductVendor tabel een samengestelde primaire-sleutelbeperking voor deze tabel. Dit zorgt ervoor dat elke rij in de ProductVendor tabel een unieke combinatie van ProductID en VendorIDheeft. Hiermee voorkomt u dat dubbele rijen worden ingevoegd.

Diagram van rijen in een tabel voor een samengestelde PRIMAIRE SLEUTEL-beperking.

  • Een tabel kan slechts één primaire sleutelbeperking bevatten.
  • Een primaire sleutel mag niet groter zijn dan 16 kolommen en een totale sleutellengte van 900 bytes.
  • De index die door een primaire-sleutelbeperking wordt gegenereerd, kan niet ertoe leiden dat het aantal indexen in de tabel groter is dan 999 niet-geclusterde indexen en 1 geclusterde index.
  • Als geclusterd of niet-geclusterd niet is opgegeven voor een primaire-sleutelbeperking, wordt geclusterd gebruikt als de tabel geen geclusterde index bevat.
  • Alle kolommen die zijn gedefinieerd in een primaire-sleutelbeperking, moeten worden gedefinieerd als niet null. Als nullbaarheid niet is opgegeven, worden alle kolommen die deelnemen aan een primaire-sleutelconstante op niet-null ingesteld.
  • Als een primaire sleutel is gedefinieerd in een door de gebruiker gedefinieerde clr-typekolom, moet de implementatie van het type binaire volgorde ondersteunen.

Beperkingen van referentiesleutels

Een vreemde sleutel (FK) is een kolom of combinatie van kolommen die wordt gebruikt om een koppeling tot stand te brengen en af te dwingen tussen de gegevens in twee tabellen, om de gegevens te beheren die kunnen worden opgeslagen in de vreemde-sleuteltabel. In een verwijzing naar een externe sleutel wordt een koppeling gemaakt tussen twee tabellen wanneer de kolom of kolommen die de primaire sleutelwaarde van een tabel bevatten, worden gerefereerd door de kolom of kolommen in een andere tabel. Deze kolom wordt een buitenlandse sleutel in de tweede tabel.

De Sales.SalesOrderHeader-tabel heeft bijvoorbeeld een vreemde sleutelkoppeling naar de Sales.SalesPerson-tabel omdat er een logische relatie is tussen verkooporders en verkopers. De kolom SalesPersonID in de SalesOrderHeader tabel komt overeen met de primaire-sleutelkolom van de SalesPerson tabel. De kolom SalesPersonID in de SalesOrderHeader tabel is de vreemde sleutel voor de SalesPerson tabel. Door deze foreign-keyrelatie te maken, kan een waarde voor SalesPersonID niet worden ingevoegd in de SalesOrderHeader tabel tenzij deze al bestaat in de SalesPerson tabel.

Een tabel kan verwijzen naar maximaal 253 andere tabellen en kolommen als refererende sleutels (uitgaande verwijzingen). SQL Server 2016 (13.x) verhoogt de limiet voor het aantal andere tabellen en kolommen dat kan verwijzen naar kolommen in één tabel (binnenkomende verwijzingen), van 253 tot 10.000. (Vereist ten minste 130 compatibiliteitsniveau.) De verhoging heeft de volgende beperkingen:

  • Er worden slechts verwijzingen naar buitenlandse sleutels van meer dan 253 ondersteund voor DELETE DML-bewerkingen. UPDATE- en MERGE-bewerkingen worden niet ondersteund.

  • Een tabel met een foreign key-referentie naar zichzelf is nog steeds beperkt tot 253 foreign key-referenties.

  • Meer dan 253 foreign key-referenties zijn momenteel niet beschikbaar voor columnstore-indexen, geheugen-geoptimaliseerde tabellen, Stretch Database of gepartitioneerde foreign key-tabel.

    Belangrijk

    Stretch Database is verouderd verklaard in SQL Server 2022 (16.x) en Azure SQL Database. Deze functie wordt verwijderd in een toekomstige versie van de database-engine. Vermijd het gebruik van deze functie in nieuwe ontwikkelwerkzaamheden en plan om toepassingen te wijzigen die momenteel gebruikmaken van deze functie.

Indexen op vreemde-sleutelbeperkingen

In tegenstelling tot primaire sleutelbeperkingen, wordt bij het maken van een vreemde sleutelbeperking niet automatisch een bijbehorende index gemaakt. Het handmatig maken van een index op een vreemde sleutel is echter vaak handig vanwege de volgende redenen:

  • Kolommen met vreemde sleutels worden vaak gebruikt bij joincriteria wanneer gegevens uit gerelateerde tabellen worden gecombineerd in query's, door één of meer kolommen in de vreemde-sleutelrelatie van een tabel te koppelen aan de primaire of unieke sleutelkolom of -kolommen in de andere tabel. Met een index kan de database-engine snel gerelateerde gegevens vinden in de foreign key-tabel. Het maken van deze index is echter niet vereist. Gegevens uit twee gerelateerde tabellen kunnen worden gecombineerd, zelfs als er geen primaire sleutel- of vreemde sleutelbeperkingen zijn gedefinieerd tussen de tabellen. Echter, een vreemde-sleutelrelatie tussen twee tabellen geeft aan dat de tabellen zijn geoptimaliseerd om te worden gecombineerd in een query die de sleutels als criteria gebruikt.

  • Wijzigingen in primaire sleutelbeperkingen worden gecontroleerd met vreemde-sleutelbeperkingen in gerelateerde tabellen.

Referentiële integriteit

Hoewel het belangrijkste doel van een beperking voor refererende sleutels is om de gegevens te beheren die kunnen worden opgeslagen in de refererende-sleuteltabel, worden ook wijzigingen in gegevens in de primaire-sleuteltabel gecontroleerd. Als de rij voor een verkoper bijvoorbeeld uit de Sales.SalesPerson tabel wordt verwijderd en de id van de verkoper wordt gebruikt voor verkooporders in de Sales.SalesOrderHeader tabel, wordt de relationele integriteit tussen de twee tabellen verbroken; de verwijderde verkooporders van de verkoper zijn zwevend in de tabel SalesOrderHeader zonder een koppeling naar de gegevens in de SalesPerson tabel.

Een beperking voor vreemde sleutels voorkomt deze situatie. De beperking dwingt referentiële integriteit af door ervoor te zorgen dat wijzigingen niet kunnen worden aangebracht in gegevens in de primaire-sleuteltabel als deze wijzigingen de koppeling naar gegevens in de refererende-sleuteltabel ongeldig maken. Als er een poging wordt gedaan om de rij in een primaire-sleuteltabel te verwijderen of een primaire-sleutelwaarde te wijzigen, mislukt de actie wanneer de verwijderde of gewijzigde primaire-sleutelwaarde overeenkomt met een waarde in de refererende-sleutelbeperking van een andere tabel. Als u een rij in een beperking voor refererende sleutels wilt wijzigen of verwijderen, moet u eerst de refererende-sleutelgegevens in de tabel met refererende sleutels verwijderen of de refererende-sleutelgegevens in de refererende-sleuteltabel wijzigen, waarmee de refererende sleutel wordt gekoppeld aan verschillende primaire-sleutelgegevens.

Trapsgewijze referentiële integriteit

Door trapsgewijze beperkingen voor referentiële integriteit te gebruiken, kunt u de acties definiëren die de database-engine uitvoert wanneer een gebruiker probeert een sleutel te verwijderen of bij te werken waarnaar bestaande refererende sleutels verwijzen. De volgende trapsgewijze acties kunnen worden gedefinieerd.

  • NO ACTION

    De database-engine genereert een fout en de verwijder- of updateactie op de rij in de bovenliggende tabel wordt teruggedraaid.

  • CASCADE

    Overeenkomende rijen worden bijgewerkt of verwijderd in de verwijzende tabel wanneer die rij wordt bijgewerkt of verwijderd in de bovenliggende tabel. CASCADE kan niet worden opgegeven als een tijdstempel kolom deel uitmaakt van de refererende sleutel of de sleutel waarnaar wordt verwezen. ON DELETE CASCADE kan niet worden opgegeven voor een tabel met een INSTEAD OF DELETE-trigger. ON UPDATE CASCADE kan niet worden opgegeven voor tabellen met INSTEAD OF UPDATE triggers.

  • SET NULL

    Alle waarden waaruit de vreemde sleutel bestaat, worden ingesteld op NULL wanneer de bijbehorende rij in de oudertabel wordt bijgewerkt of verwijderd. Opdat deze beperking uitgevoerd kan worden, moeten de kolommen met vreemde sleutels null-waarden kunnen bevatten. Kan niet worden opgegeven voor tabellen met INSTEAD OF UPDATE-triggers.

  • SET DEFAULT

    Alle waarden waaruit de vreemde sleutel bestaat, worden ingesteld op hun standaardwaarden als de bijbehorende rij in de bovenliggende tabel wordt bijgewerkt of verwijderd. Om deze beperking uit te voeren, moeten alle kolommen met buitenlandse sleutels standaarddefinities hebben. Als een kolom nullable is en er geen expliciete standaardwaarde is ingesteld, wordt NULL de impliciete standaardwaarde van de kolom. Het kan niet worden opgegeven voor tabellen met INSTEAD OF UPDATE-triggers.

CASCADE, SET NULL, SET DEFAULTen NO ACTION kunnen worden gecombineerd in tabellen die referentiële relaties met elkaar hebben. Als de database-engine NO ACTIONtegenkomt, stopt het de gerelateerde CASCADE-, SET NULL- en SET DEFAULT-acties en draait ze terug. Wanneer een DELETE-instructie een combinatie van CASCADE, SET NULL, SET DEFAULTof NO ACTION acties veroorzaakt, worden alle CASCADE, SET NULLen SET DEFAULT acties toegepast voordat de database-engine op een NO ACTIONcontroleert.

Triggers en trapsgewijze referentiële acties

Met trapsgewijze referentiële acties worden de AFTER UPDATE of AFTER DELETE triggers op de volgende manier geactiveerd:

  • Alle trapsgewijze referentiële acties die rechtstreeks worden veroorzaakt door de oorspronkelijke DELETE of UPDATE worden eerst uitgevoerd.

  • Als er AFTER triggers zijn gedefinieerd in de betrokken tabellen, worden deze triggers geactiveerd nadat alle trapsgewijze acties zijn uitgevoerd. Deze triggers worden geactiveerd in de tegenovergestelde volgorde van de kettingreactie. Als er meerdere triggers in één tabel staan, worden ze in willekeurige volgorde geactiveerd, tenzij er een speciale eerste of laatste trigger voor de tabel is. Deze volgorde is zoals opgegeven met behulp van sp_settriggerorder.

  • Als meerdere trapsgewijze ketens afkomstig zijn uit de tabel die het directe doel was van een UPDATE of DELETE actie, is de volgorde waarin deze ketens hun respectieve triggers activeren niet opgegeven. De ene ketting brandt echter altijd alle triggers voordat een andere ketting begint te branden.

  • Een AFTER trigger in de tabel die het directe doel is van een UPDATE of DELETE actie wordt geactiveerd, ongeacht of er rijen worden beïnvloed. In dit geval worden er geen andere tabellen beïnvloed door trapsgewijze wijzigingen.

  • Als een van de vorige triggers UPDATE of DELETE bewerkingen op andere tabellen uitvoert, kunnen deze acties secundaire trapsgewijze ketens starten. Deze secundaire ketens worden voor elke UPDATE of DELETE bewerking tegelijk verwerkt nadat alle triggers op alle primaire kettingen worden geactiveerd. Dit proces kan recursief worden herhaald voor volgende UPDATE of DELETE bewerkingen.

  • Het uitvoeren van CREATE, ALTER, DELETEof andere DDL-bewerkingen (Data Definition Language) binnen de triggers kan ervoor zorgen dat DDL-triggers worden geactiveerd. Hierdoor kunnen delete- of UPDATE-bewerkingen worden uitgevoerd waarmee extra trapsgewijze ketens en triggers worden gestart.

  • Als er een fout optreedt in een bepaalde cascaderende verwijzingsketen, ontstaat er een fout, worden er geen AFTER triggers geactiveerd in die keten en wordt de DELETE- of UPDATE-bewerking die de keten initieerde, teruggedraaid.

  • Een tabel met een INSTEAD OF trigger kan niet ook een REFERENCES component hebben waarmee een trapsgewijze actie wordt opgegeven. Een AFTER trigger voor een tabel waarop een trapsgewijze actie is gericht, kan echter een INSERT, UPDATEof DELETE instructie uitvoeren in een andere tabel of weergave die een INSTEAD OF trigger activeert die is gedefinieerd voor dat object.

Verwante inhoud