- 07/25/2017
- 9 minuten om te lezen
-
-
s -
M -
M -
c -
J -
+1
-
Geldt voor: 
SQL Server 2016 (13.x) en later 
Azure SQL Database Azure SQL Managed Instance
Primary keys en foreign keys zijn twee soorten constraints die kunnen worden gebruikt om de gegevensintegriteit in SQL Server-tabellen af te dwingen. Dit zijn belangrijke databaseobjecten.
Dit onderwerp bevat de volgende secties.
Primary Key Constraints
Foreign Key Constraints
Gerelateerde taken
Primary Key Constraints
Een tabel heeft meestal een kolom of een combinatie van kolommen die waarden bevatten die elke rij in de tabel op unieke wijze identificeren. Deze kolom, of kolommen, wordt de primaire sleutel (PK) van de tabel genoemd en handhaaft de integriteit van de tabel. Omdat primaire sleutel constraints unieke gegevens garanderen, worden ze vaak gedefinieerd op een identiteitskolom.
Wanneer u een primaire sleutel constraint specificeert voor een tabel, dwingt de Database Engine de uniciteit van gegevens af door automatisch een unieke index aan te maken voor de primaire-sleutel kolommen. Deze index zorgt ook voor snelle toegang tot gegevens wanneer de primaire sleutel in queries wordt gebruikt. Als een primaire sleutel constraint is gedefinieerd op meer dan één kolom, mogen waarden binnen één kolom worden gedupliceerd, maar elke combinatie van waarden uit alle kolommen in de primaire sleutel constraint definitie moet uniek zijn.
Zoals te zien in de volgende illustratie, vormen de ProductID en VendorID kolommen in de Purchasing.ProductVendor tabel een samengestelde primaire sleutel constraint voor deze tabel. Dit zorgt ervoor dat elke rij in de ProductVendor tabel een unieke combinatie van ProductID en VendorID heeft. Dit voorkomt het invoegen van dubbele rijen.
-
Een tabel kan slechts één primary key constraint bevatten.
-
Een primary key mag niet langer zijn dan 16 kolommen en een totale sleutellengte van 900 bytes.
-
De index die door een primaire-sleutelrestrictie wordt gegenereerd, mag er niet toe leiden dat het aantal indexen in de tabel groter is dan 999 niet-geclusterde indexen en 1 geclusterde index.
-
Als voor een primaire-sleutelrestrictie geen geclusterde of niet-geclusterde index is opgegeven, wordt een geclusterde index gebruikt als er geen geclusterde index op de tabel aanwezig is.
-
Alle kolommen die binnen een primaire-sleutelrestrictie zijn gedefinieerd, moeten als niet-nullig worden gedefinieerd. Als nullability niet is gespecificeerd, wordt de nullability van alle kolommen in een primaire-sleutelrestrictie ingesteld op not null.
-
Als een primaire sleutel is gedefinieerd op een door de gebruiker gedefinieerde kolom van het CLR-type, moet de implementatie van het type binaire ordening ondersteunen.
Foreign Key Constraints
Een foreign key (FK) is een kolom of combinatie van kolommen die wordt gebruikt om een koppeling tussen de gegevens in twee tabellen tot stand te brengen en af te dwingen om te bepalen welke gegevens in de foreign key-tabel kunnen worden opgeslagen. In een foreign key verwijzing wordt een verband gelegd tussen twee tabellen wanneer de kolom of kolommen die de primaire-sleutelwaarde voor de ene tabel bevatten, worden verwezen door de kolom of kolommen in een andere tabel. Deze kolom wordt een foreign key in de tweede tabel.
Bijv. de tabel Sales.SalesOrderHeader heeft een foreign key-koppeling met de tabel Sales.SalesPerson omdat er een logische relatie bestaat tussen verkooporders en verkopers. De kolom SalesPersonID in de tabel SalesOrderHeader komt overeen met de primaire sleutelkolom van de tabel SalesPerson. De kolom SalesPersonID in de tabel SalesOrderHeader is de foreign key naar de tabel SalesPerson. Door deze foreign key-relatie te maken, kan een waarde voor SalesPersonID niet in de SalesOrderHeader-tabel worden ingevoegd als deze nog niet bestaat in de SalesPerson-tabel.
Een tabel kan maximaal 253 andere tabellen en kolommen als foreign key (uitgaande verwijzingen) verwijzen. SQL Server 2016 (13.x) verhoogt de limiet voor het aantal andere tabellen en kolommen dat kan verwijzen naar kolommen in een enkele tabel (inkomende verwijzingen), van 253 naar 10.000. (Vereist ten minste compatibiliteitsniveau 130.) De verhoging heeft de volgende beperkingen:
-
Meer dan 253 foreign key-referenties worden alleen ondersteund voor DELETE DML-bewerkingen. UPDATE- en MERGE-bewerkingen worden niet ondersteund.
-
Een tabel met een foreign key-verwijzing naar zichzelf is nog steeds beperkt tot 253 foreign key-verwijzingen.
-
Meer dan 253 foreign key-verwijzingen zijn momenteel niet beschikbaar voor columnstore-indexen, voor geheugengeoptimaliseerde tabellen, voor Stretch Database of voor gepartitioneerde foreign key-tabellen.
Indexen op foreign key-constraints
In tegenstelling tot primaire-sleutelconstraints wordt er bij het maken van een foreign key-constraint niet automatisch een bijbehorende index gemaakt. Het handmatig maken van een index op een foreign key is echter vaak nuttig om de volgende redenen:
-
Foreign key-kolommen worden vaak gebruikt in join-criteria wanneer de gegevens van gerelateerde tabellen in query’s worden gecombineerd door de kolom of kolommen in de foreign key constraint van de ene tabel te matchen met de primary of unique key kolom of kolommen in de andere tabel. Een index stelt de Database Engine in staat om snel gerelateerde gegevens te vinden in de foreign key tabel. Het aanmaken van deze index is echter niet vereist. Gegevens uit twee verwante tabellen kunnen worden gecombineerd, zelfs als er geen primaire sleutel of foreign key constraints tussen de tabellen zijn gedefinieerd, maar een foreign key-relatie tussen twee tabellen geeft aan dat de twee tabellen zijn geoptimaliseerd om te worden gecombineerd in een query waarin de sleutels als criteria worden gebruikt.
-
Veranderingen aan primaire-sleutelrestricties worden gecontroleerd met behulp van foreign-sleutelrestricties in gerelateerde tabellen.
Referentiële integriteit
Hoewel het belangrijkste doel van een foreign-sleutelrestrictie is om de gegevens te controleren die in de foreign-sleutel tabel kunnen worden opgeslagen, controleert deze ook wijzigingen aan gegevens in de primaire-sleutel tabel. Als bijvoorbeeld de rij voor een verkoper wordt verwijderd uit de tabel Sales.SalesPerson, en de ID van de verkoper wordt gebruikt voor verkooporders in de tabel Sales.SalesOrderHeader, wordt de relationele integriteit tussen de twee tabellen verbroken; de verwijderde verkooporders van de verkoper worden verweesd in de tabel SalesOrderHeader zonder een koppeling naar de gegevens in de tabel SalesPerson.
Een foreign key constraint voorkomt deze situatie. De constraint dwingt referentiële integriteit af door te garanderen dat er geen wijzigingen kunnen worden aangebracht in gegevens in de primaire sleuteltabel als die wijzigingen de koppeling met gegevens in de foreign key-tabel ongeldig maken. Als een poging wordt ondernomen om een rij in een primary key tabel te verwijderen of een primary key waarde te wijzigen, zal de actie mislukken als de verwijderde of gewijzigde primary key waarde overeenkomt met een waarde in de foreign key constraint van een andere tabel. Om met succes een rij in een foreign key constraint te wijzigen of te verwijderen, moet u eerst ofwel de foreign key gegevens in de foreign key tabel verwijderen of de foreign key gegevens in de foreign key tabel wijzigen, die de foreign key aan andere primaire key gegevens koppelt.
Cascadeerbare referentiële integriteit
Door cascadeerbare referentiële integriteit constraints te gebruiken, kunt u de acties definiëren die de Database Engine onderneemt als een gebruiker probeert een sleutel te verwijderen of bij te werken waarnaar bestaande foreign keys verwijzen. De volgende cascade acties kunnen worden gedefinieerd.
Geen actie
De Database Engine geeft een foutmelding en de verwijderde of bijgewerkte actie op de rij in de bovenliggende tabel wordt teruggedraaid.
CASCADE
Corresponderende rijen worden bijgewerkt of verwijderd in de tabel waarnaar wordt verwezen wanneer die rij in de bovenliggende tabel wordt bijgewerkt of verwijderd. CASCADE kan niet worden opgegeven als een tijdstempelkolom deel uitmaakt van de foreign key of de tabel waarnaar wordt verwezen. ON DELETE CASCADE kan niet worden opgegeven voor een tabel die een INSTEAD OF DELETE-trigger heeft. ON UPDATE CASCADE kan niet worden opgegeven voor tabellen die een INSTEAD OF UPDATE trigger hebben.
SET NULL
Alle waarden die deel uitmaken van de vreemde sleutel worden op NULL gezet wanneer de corresponderende rij in de bovenliggende tabel wordt bijgewerkt of verwijderd. Om deze restrictie uit te voeren, moeten de foreign key kolommen nullable zijn. Kan niet worden opgegeven voor tabellen die INSTEAD OF UPDATE triggers hebben.
SET DEFAULT
Alle waarden die deel uitmaken van de foreign key worden op hun standaardwaarden gezet als de corresponderende rij in de bovenliggende tabel wordt bijgewerkt of verwijderd. Om deze restrictie uit te voeren, moeten alle foreign key kolommen standaard definities hebben. Als een kolom nullable is, en er is geen expliciete standaardwaarde ingesteld, wordt NULL de impliciete standaardwaarde van de kolom. Kan niet worden gespecificeerd voor tabellen die INSTEAD OF UPDATE-triggers hebben.
CASCADE, SET NULL, SET DEFAULT en NO ACTION kunnen worden gecombineerd op tabellen die referentiële relaties met elkaar hebben. Als de Database Engine NO ACTION tegenkomt, stopt hij en rolt hij gerelateerde CASCADE-, SET NULL- en SET DEFAULT-acties terug. Wanneer een DELETE-instructie een combinatie van CASCADE-, SET NULL-, SET DEFAULT- en NO ACTION-acties veroorzaakt, worden alle CASCADE-, SET NULL- en SET DEFAULT-acties toegepast voordat de Database Engine controleert op NO ACTION.
Triggers en cascade-referentiële acties
Cascade-referentiële acties vuren de AFTER UPDATE- of AFTER DELETE-triggers op de volgende manier af:
-
Alle cascade-referentiële acties die direct door de oorspronkelijke DELETE of UPDATE worden veroorzaakt, worden eerst uitgevoerd.
-
Als er AFTER-triggers zijn gedefinieerd voor de betrokken tabellen, worden deze triggers geactiveerd nadat alle cascadeacties zijn uitgevoerd. Deze triggers worden geactiveerd in de omgekeerde volgorde van de cascadeacties. Als er meerdere triggers op een enkele tabel zijn, vuren ze in willekeurige volgorde, tenzij er een specifieke eerste of laatste trigger voor de tabel is. Deze volgorde wordt opgegeven met sp_settriggerorder.
-
Als meerdere cascadeketens afkomstig zijn van de tabel die het directe doel was van een UPDATE- of DELETE-actie, is de volgorde waarin deze ketens hun respectieve triggers afvuren niet gespecificeerd.
-
Een AFTER-trigger voor de tabel die het directe doel is van een UPDATE- of DELETE-actie, wordt altijd gestart, ongeacht of er rijen zijn beïnvloed. In dit geval zijn er geen andere tabellen die door cascade worden beïnvloed.
-
Als een van de voorgaande triggers UPDATE- of DELETE-bewerkingen op andere tabellen uitvoert, kunnen deze acties secundaire cascadeketens starten. Deze secundaire ketens worden voor elke UPDATE- of DELETE-bewerking tegelijk verwerkt nadat alle triggers van alle primaire ketens zijn afgegaan. Dit proces kan recursief worden herhaald voor volgende UPDATE- of DELETE-bewerkingen.
-
Het uitvoeren van CREATE-, ALTER-, DELETE- of andere DDL-bewerkingen (Data Definition Language) binnen de triggers kan DDL-triggers laten afgaan. Hierdoor kunnen vervolgens DELETE- of UPDATE-bewerkingen worden uitgevoerd die aanvullende cascadeketens en -triggers starten.
-
Als binnen een bepaalde cascadeketen een fout wordt gegenereerd, wordt een fout opgeworpen, worden er geen AFTER-triggers in die keten gestart en wordt de DELETE- of UPDATE-bewerking die de keten heeft gecreëerd, teruggedraaid.
-
Een tabel met een INSTEAD OF-trigger kan niet ook een REFERENCES-clausule hebben waarin een cascadeactie is gespecificeerd. Een AFTER-trigger op een tabel met een cascade-actie kan echter een INSERT-, UPDATE- of DELETE-instructie uitvoeren op een andere tabel of weergave die een INSTEAD OF-trigger afvuurt die op dat object is gedefinieerd.
Verwante taken
De volgende tabel bevat een lijst met veelvoorkomende taken die verband houden met primaire-sleutel- en foreign-sleutelrestricties.
| Taak | Topic |
|---|---|
| Beschrijft hoe je een primaire sleutel maakt. | Create Primary Keys |
| beschrijft hoe je een primary key kunt verwijderen. | Delete Primary Keys |
| beschrijft hoe je een primary key kunt wijzigen. | Bewerken aan primaire sleutels |
| beschrijft hoe je vreemde-sleutelrelaties aanmaakt | Buitenlandse-sleutelrelaties aanmaken |
| beschrijft hoe je vreemde-sleutelrelaties wijzigt. | Verander foreign key relaties |
| Verwijder foreign key relaties | |
| Bekijk de eigenschappen van een vreemde sleutel | |
| Sluit beperkingen van een vreemde sleutel voor replicatie uit | |
| Sluit Foreign Key Constraints uit met INSERT- en UPDATE-opdrachten |