Klucz obcy – informacje praktyczne | foreign key – practical

By dokładniej omówić jak działa klucz obcy posłużę się przykładem. Weźmy po uwagę 2 tabele: Klienci i ich adresy.
Załóżmy że zbieramy informację o adresach do korespondencji i adresach zameldowania. Możemy to zrealizować na 2 sposoby. Albo utworzymy tabelę klienci która będzie zawierała odpowiednie kolumny (odpowiedzialne za adres do korespondencji i adres zameldowania) lub też stworzymy 2 tabele, gdzie w jednej będziemy trzymać dane klienta a w drugiej dane adresowe. Drugi przypadek jest o tyle ciekawszy że w momencie jak będziemy chcieli zbierać więcej niż 2 adresy nie musimy przebudowywać aplikacji i bazy danych by dodać taką możliwość. Jednak taka struktura wymaga( a właściwie stwarza możliwość) wykorzystania klucza podstawowego i obcego. Dla zainteresowanych teorią tego zagadnienia jak prawidłowo rozdzielać kolumny pomiędzy tabelami i jak tworzyć relację polecam hasło: 3 postać normalna (3NF), zapraszam także do lektury moich postów o modelu relacyjnym.

Wracając do praktyki, poniższe skrypty są tworzone pod SQL Server, jednak po niewielkich przeróbkach mogą aplikowane w innych bazach danych.


create table klienci
(klient_id int not null primary key identity(1,1),
klient_imie varchar(255),
klient_data datetime default getdate()
)

create table adresy (
adres_id int not null primary key identity(1,1),
adres_ulica varchar(255),
adres_miejscowosc varchar(255),
adres_klient_id int,
adres_typ int
)

insert into klienci (klient_imie) values ('Krzys');
insert into klienci (klient_imie) values ('Adam');
insert into klienci (klient_imie) values ('Andrzej');
insert into klienci (klient_imie) values ('Marek');

insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Poznan','Poznanska',1,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Warszawa','Warszawska',2,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gniezno','Dworcowa',1,2);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Krakow','Glowna',1,3);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gdansk','Bursztynowa',1,4)
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Olsztyn','Polska',1,5)

Uruchomienie powyższych zapytań tworzy nam dwie tabele. Tabela z danymi klientów (celowo uproszczona) oraz prosta tabela z adresami. Jak widać w tabeli adresy jest kolumna wskazująca na klienta (adres_klient_id). Jest to kolumna na której powinniśmy założyć klucz obcy.

Najpierw jednak przyjrzyjmy się danym w tabelach.

Jak widać w tabeli adresów znajduje się jedno wskazanie na id klienta który nie istnieje w tabeli klientów. Do takiej sytuacji łatwo doprowadzić w sytuacji gdy baza nie posiada więzów integralności (czyli właśnie kluczy obcych i podstawowych). Taki efekt możemy uzyskać np w sytuacji gdy ktoś po
prostu usunął klient z klient_id=5.

Możemy spróbować założyć klucz obcy na tabelę adresy by uniknąć takich sytuacji:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)

Składnia polecenia jest dość prosta:
Alter table nazwa_tabeli – wskazanie tabeli na której chcemy założyć klucz.
add constraint – rodzaj operacji.
adresy_klient_fk – nazwa naszego klucza, wybieramy ją sami, proszę pamiętać że nazwa klucza nie może powielać się z innymi obiektami w bazie.
foreign key (adres_klient_id) – typ klucza, constraint – więzu integralności, w nawiasie nazwa kolumny na którą zakładamy klucz.
references klienci(klient_id) – wskazanie na tabelę i kolumnę do której klucz obcy ma się odnosić.
Typ klucza podstawowego i obcego w sql server zawsze muszą być identyczne (uwzględniając długość łańcuchów varchar).

Nie mniej ze względu na nasz błąd w danych dostaniemy komunikat błędu:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.klienci”, column ‘klient_id’.

Aby poradzić sobie z taką sytuacją mamy dwa wyjścia. Możemy stwierdzić że nie jesteśmy wstanie naprawić bałaganu, ale nie chcemy go też powiększać. Dlatego chcielibyśmy utworzyć klucz który będzie działał na nowo wprowadzone dane:

alter table adresy with nocheck
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)

w tej wersji zapytania pojawiły się słowa with nocheck, jest to informacja dla bazy danych że ma nie weryfikować już wprowadzonych danych.

Gdy utworzyliśmy taki klucz, polecenie:
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Olsztyn','Polska',1,6)

zwróci nam:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.klienci”, column ‘klient_id’.
The statement has been terminated.

klient_id = 6 nie istnieje w tabeli klienci więc baza nie pozwoli nam na zapis takiej informacji.

Drugi scenariusz jaki możemy przeprowadzić to próba posprzątania bałaganu, tutaj mamy dwie opcje. Możemy usunąć kłopotliwy wiersz, lub też możemy ustawić wartość adres_klient_id na null. Czyli na wartość nieokreśloną. W ten sposób zachowamy adres i zweryfikujemy poprawność pozostałych danych.

By przeprowadzić scenariusz numer 2, usuniemy na chwilę klucz obcy:

alter table adresy
drop constraint adresy_klient_fk

teraz ustawmy wartosc null dla danych które nie spełniają zależności klucza obcego:

update adresy set adres_klient_id = null where adres_klient_id not in (select klient_id from klienci)

Teraz możemy założyć klucz obcy według standardowych ustawień:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)
.

Możemy zauważyć że relacja która właśnie stworzyliśmy jest typu jeden do wiele. W tabeli klienci mamy zawsze wartości unikalne (wymóg klucza podstawowego). W tabeli adresy możemy mieć wiele adresów przypisanych do 1 klienta. Jest to najpopularniejszy typ relacji.

Spróbujmy teraz usunąć klienta z naszej bazy:

delete from klienci where klient_id = 2

Baza zwróci nam komunikat:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.adresy”, column ‘adres_klient_id’.
The statement has been terminated.

W ten sposób już nie osierocimy adresów, jednak czasem mimo wszystko ktoś może chcieć usunąć dane z bazy. Wtedy musimy usuwać dane w kierunku relacji. Czyli zaczynamy od tabel które nie mają na siebie wskazań (ich klucz podstawowy nie jest nigdzie kluczem obcym). W naszym przykładzie taką tabelą jest tabela adresy.

delete from adresy where adres_klient_id = 2
delete from klienci where klient_id = 2

Taki kod wykonany w takiej kolejności zadziała prawidłowo. Warto jednak dodać iż warto go objąć tak zwaną transakcją by nie osierocić klienta z adresu. Możemy bowiem wyobrazić sobie sytuację w które adres udaje nam się usunąć a dane klienta już nie.

begin transaction
delete from adresy where adres_klient_id = 2
delete from klienci where klient_id = 2
commit

Powyższy kod jest najbardziej poprawną wersją.

Istnieje jeszcze możliwość kaskadowego usuwania danych.
Usuńmy na chwilę nasz klucz obcy:

alter table adresy
drop constraint adresy_klient_fk

dodajmy go z powrotem, tym razem z opcją kaskadowego usuwania danych.

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id) on delete cascade

teraz wykonanie polecenia:
delete from klienci where klient_id=1

Niestanowi żadnego problemu, możemy też zauważyć że automatycznie został usunięty wiersz z tabeli adresy ( w naszym wypadku nawet dwa wiersze).

Możemy też tak skonfigurować nasz klucz by podczas usuwania danych z tabeli klienci, ustawiał wartość null w kolumnie adres_klient_id. Aby tego dokonać w naszym przykładzie usuńmy po raz kolejny nasz klucz:

alter table adresy
drop constraint adresy_klient_fk

Teraz dodajmy klucz z opcją on delete set null:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id) on delete set null

Po ustawieniu klucza, wykonujemy delete z tabeli klientów:

delete from klienci where klient_id = 3

Po wykonaniu zapytania, widać że baza ustawiła nam wartość null dla adresu z adres_klient_id=3.

Leave a Reply

Your email address will not be published. Required fields are marked *

nineteen + 11 =