Microsoft SQL Server
Buitenlandse sleutels

Buitenlandse sleutelrelatie / beperking

Met externe sleutels kunt u de relatie tussen twee tabellen definiëren. Eén (bovenliggende) tabel moet een primaire sleutel hebben die op unieke wijze rijen in de tabel identificeert. Een andere (onderliggende) tabel kan de waarde van de primaire sleutel van de ouder hebben in een van de kolommen. Beperking van FOREIGN KEY REFERENTIES zorgt ervoor dat waarden in onderliggende tabel moeten bestaan als primaire sleutelwaarde in de bovenliggende tabel.

In dit voorbeeld hebben we een bovenliggende Company-tabel met de primaire sleutel CompanyId en een onderliggende Employee-tabel met een ID van het bedrijf waar deze werknemer werkt.

create table Company (
   CompanyId int primary key,
   Name nvarchar(200)
)
create table Employee (
    EmployeeId int,
    Name nvarchar(200),
    CompanyId int
        foreign key references Company(companyId)
)

referenties met buitenlandse sleutels zorgen ervoor dat waarden die zijn ingevoegd in de kolom Employee.CompanyId ook moeten bestaan in de kolom Company.CompanyId. Ook kan niemand het bedrijf in de bedrijfstabel verwijderen als er ten minste één werknemer is met een bijbehorend bedrijfs-ID in de onderliggende tabel.

BUITENLANDSE SLEUTEL-relatie zorgt ervoor dat rijen in twee tabellen niet "ontkoppeld" kunnen worden.

Relatie onderhouden tussen bovenliggende / onderliggende rijen

Laten we aannemen dat we één rij hebben in de bedrijfstabel met companyId 1. We kunnen een rij invoegen in de medewerkerstabel met companyId 1:

insert into Employee values (17, 'John', 1)

We kunnen echter geen werknemer invoegen die een niet-bestaande CompanyId heeft:

insert into Employee values (17, 'John', 111111)

Msg 547, niveau 16, staat 0, regel 12 De INSERT-instructie is in strijd met de FOREIGN KEY-beperking "FK__Employee__Compan__1EE485AA". Het conflict vond plaats in de database "MyDb", tabel "dbo.Company", kolom 'CompanyId'. De verklaring is beëindigd.

We kunnen ook de bovenliggende rij in de bedrijfstabel niet verwijderen zolang er ten minste één onderliggende rij in de medewerkertabel is die ernaar verwijst.

delete from company where CompanyId = 1

Msg 547, niveau 16, staat 0, regel 14 De instructie DELETE is in strijd met de REFERENTIEbeperking "FK__Employee__Compan__1EE485AA". Het conflict vond plaats in de database "MyDb", tabel "dbo.Employee", kolom 'CompanyId'. De verklaring is beëindigd.

Een externe sleutelrelatie zorgt ervoor dat bedrijfs- en werknemersrijen niet worden "ontkoppeld".

Buitenlandse sleutelrelatie toevoegen aan bestaande tabel

BUITENLANDSE SLEUTELbeperking kan worden toegevoegd aan bestaande tabellen die nog steeds geen relatie hebben. Stel je voor dat we Bedrijf- en Medewerker-tabellen hebben waarin Medewerker-tabel BedrijfId-kolom maar geen externe sleutelrelatie hebben. Met de instructie ALTER TABLE kunt u een externe sleutelbeperking toevoegen aan een bestaande kolom die verwijst naar een andere tabel en primaire sleutel in die tabel:

alter table Employee
    add  foreign key (CompanyId) references Company(CompanyId)

Voeg een externe sleutel toe aan de bestaande tabel

BUITENLANDSE SLEUTELkolommen met beperking kunnen worden toegevoegd aan bestaande tabellen die nog steeds geen relatie hebben. Stel je voor dat we bedrijfstabellen en werknemerstabellen hebben waar de tabel met medewerkers geen kolom CompanyId heeft. Met de instructie ALTER TABLE kunt u een nieuwe kolom toevoegen met een externe sleutelbeperking die verwijst naar een andere tabel en primaire sleutel in die tabel:

alter table Employee
    add CompanyId int foreign key references Company(CompanyId)

Informatie krijgen over beperkingen van externe sleutels

sys.foreignkeys systeemweergave geeft informatie terug over alle relaties met buitenlandse sleutels in de database:

select name,
 OBJECT_NAME(referenced_object_id) as [parent table],
 OBJECT_NAME(parent_object_id) as [child table],
 delete_referential_action_desc,
 update_referential_action_desc
from sys.foreign_keys