SQL FOREIGN KEY (klucz obcy)

SQL FOREIGN KEY


    


DEFINICJA

Klucz obcy służy do definiowania relacji między tabelami. Kolumnę (lub kolumny) którą zdefiniujemy jako klucz obcy w jednej tabeli wiążemy z kolumną (kolumnami) która jest kluczem głównym w drugiej tabeli. Oznacza to, że wartości przechowywane w kolumnie która jest zdefinowana jako klucz obcy w pierwszej tabeli zawsze będą miały swój odpowiednik w kolumnie która jest zdefiniowana jako klucz główny w drugiej tabeli.

Żeby lepiej zrozumieć ten mechanizm posłużę się przykładem. Wyobraź sobie, że mamy dwie tabele. Jedna o nazwie "Pracownicy" i druga o nazwie "Miasta". Tabla "Pracownicy" przechowuje informacje na temat pracowników. W jednej z kolumn chcemy przechowywać informacje na temat miasta w którym mieszka nasz pracownik. Ale zamiast wpisywać w każdym rekordzie pełną nazwę miasta które będą się powtarzać stworzymy nową tabelę o nazwie "Miasta" gdzie będziemy przechowywać pełne nazwy miast i w której nazwy miast nie będą dublowane. Za pomoca kluczy obcego i głównego połączymy te tabele. Przeanalizuje poniższy obrazek.

sql foreign key
Żeby nie dublować i przechowywać pełnych nazw miast w tabeli "Pracownicy" w kolumnie "ID_miasto" będziemy przechowywali identyfikator miasta z tabeli "Miasta". W naszym przykładzie kolumne "ID_miasto" z tabeli "Pracownicy" musimy zdefiniować jako klucz obcy a kolumnę "ID_miasto" z tabeli "Miasta" musimy zdefiniować jako klucz główny tej tabeli.

Oba typu kluczy możemy utworzyć w momencie tworzenia tabeli lub dodać je już na gotowej tabeli.

Na początku zajmiemy się sytuacją w której tworzymy nowe tabele od początku. Tworzenie tabel musimy rozpocząć od tabeli "Miasta" bo to do niej będziemy tworzyli relację z tabeli "Pracownicy". Nie możemy utworzyć tabeli w której tworzymy relację do tabeli która jeszcze nie istnieje bo otrzymamy komunikat o błędzie. Teraz utworzymy tabelę "Miasta" i definiujemy kolumnę "ID_miasto" jako klucz główny tej tabeli, bo to wartości w tej kolumnie będą jednoznacznie identyfikować rekordy w tej tabeli.

CREATE TABLE Miasta
(
      ID_miasto int NOT NULL
     ,nazwa
varchar(40) NOT NULL
     ,PRIMARY KEY (ID_miasto)
)

teraz utworzymy tabele "Pracownicy" gdzie zdefiniujemy klucz obcy na kolumnie "ID_miasto" i ustawimy relację tej kolumny z kolumną "ID_miasto" z tabeli "Miasta" i klucz główny na kolumnie "ID_pracownik" w tabeli "Pracownicy".

CREATE TABLE Pracownicy
(
      ID_pracownik int NOT NULL
     ,Nazwisko varchar(40)
NOT NULL
     ,Imie varchar(20) NOT NULL
     ,ID_miasto int NOT NULL
     ,PRIMARY KEY (ID_pracownik)
    , CONSTRAINT fk_PracownikMiasto FOREIGN KEY (ID_miasto)
     REFERENCES Miasta(ID_miasto)
)

Zdefiniowanie klucza obcego możemy dokonać na istniejącej już tabeli. Powiedzmy, że mamy stworzone już tabele "Pracownicy" i "Miasta" i chcemy zdefiniować klucz obcy na kolumnie "ID_miasto" w tabeli "Pracownicy" i ustawić relację do klucza głównego (którym jest kolumna "ID_miasto") w tabeli "Miasta".

ALTER TABLE Pracownicy
ADD CONSTRAINT
fk_PracownikMiasto FOREIGN KEY (ID_miasto) REFERENCES Miasta(ID_miasto)

w celu usunięcia tego klucza posłużymy się poniższym kodem

ALTER TABLE Pracownicy
DROP FOREIGN KEY fk_PracownikMiasto