Microsoft SQL Server
Klucz obcy

Relacja / ograniczenie klucza obcego

Klucze obce umożliwiają zdefiniowanie relacji między dwiema tabelami. Jedna (nadrzędna) tabela musi mieć klucz podstawowy, który jednoznacznie identyfikuje wiersze w tabeli. Inna (podrzędna) tabela może mieć wartość klucza podstawowego nadrzędnego w jednej z kolumn. Ograniczenia ODNIESIENIA KLUCZOWE zapewniają, że wartości w tabeli podrzędnej muszą istnieć jako wartość klucza podstawowego w tabeli nadrzędnej.

W tym przykładzie mamy tabelę nadrzędną firmy z kluczem podstawowym CompanyId i podrzędną tabelę pracownika, która ma identyfikator firmy, w której pracuje ten pracownik.

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)
)

odniesienia do klucza obcego zapewniają, że wartości wstawione w kolumnie Employee.CompanyId muszą również istnieć w kolumnie Company.CompanyId. Ponadto nikt nie może usunąć firmy z tabeli firmy, jeśli w tabeli podrzędnej jest co najmniej jeden pracownik z pasującą firmą.

Relacja ZAGRANICZNY zapewnia, że wierszy w dwóch tabelach nie można „rozłączyć”.

Utrzymywanie relacji między wierszami nadrzędnymi / podrzędnymi

Załóżmy, że mamy jeden wiersz w tabeli firmowej z CompanyId 1. Możemy wstawić wiersz w tabeli pracowników, która ma companyId 1:

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

Nie możemy jednak wstawić pracownika, który nie ma CompanyId:

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

Msg 547, poziom 16, stan 0, wiersz 12 Instrukcja INSERT kolidowała z ograniczeniem klucza OBCEGO „FK__Employee__Compan__1EE485AA”. Konflikt wystąpił w bazie danych „MyDb”, tabeli „dbo.Company”, kolumnie „CompanyId”. Instrukcja została zakończona.

Nie możemy również usunąć wiersza nadrzędnego w tabeli firmy, dopóki istnieje co najmniej jeden wiersz podrzędny w tabeli pracowników, który się do niego odwołuje.

delete from company where CompanyId = 1

Msg 547, poziom 16, stan 0, wiersz 14 Instrukcja DELETE była w konflikcie z ograniczeniem REFERENCE „FK__Employee__Compan__1EE485AA”. Konflikt wystąpił w bazie danych „MyDb”, tabeli „dbo.Employee”, kolumnie „CompanyId”. Instrukcja została zakończona.

Relacja klucza obcego zapewnia, że wiersze firmy i pracownika nie zostaną „odłączone”.

Dodanie relacji klucza obcego do istniejącej tabeli

Ograniczenie KLUCZ ZAGRANICZNY można dodać do istniejących tabel, które nadal nie są ze sobą powiązane. Wyobraź sobie, że mamy tabele firm i pracowników, w których kolumna tabeli pracowników CompanyId nie ma relacji z kluczem obcym. Instrukcja ALTER TABLE umożliwia dodanie ograniczenia klucza obcego do istniejącej kolumny, która odwołuje się do innej tabeli i klucza podstawowego w tej tabeli:

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

Dodaj klucz obcy do istniejącej tabeli

Kolumny ZAGRANICZNE z ograniczeniami można dodawać do istniejących tabel, które nadal nie są ze sobą powiązane. Wyobraź sobie, że mamy tabele firm i pracowników, w których tabela pracowników nie ma kolumny CompanyId. Instrukcja ALTER TABLE umożliwia dodanie nowej kolumny z ograniczeniem klucza obcego, który odwołuje się do innej tabeli i klucza podstawowego w tej tabeli:

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

Uzyskiwanie informacji o ograniczeniach klucza obcego

Widok systemu sys.foreignkeys zwraca informacje o wszystkich relacjach kluczy obcych w bazie danych:

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