Kurs SQL i MySQL · Moduł 9: Indeksy i normalizacja
Klucze obce i integralność danych
W tej lekcji3
Nasza Biblioteka to sieć odsyłaczy. Zwój książki nie powtarza całego życiorysu autora - zamiast tego wskazuje sygnaturę zwoju z opisem twórcy. W MySQL takim odsyłaczem jest klucz obcy (foreign key).
Czym jest klucz obcy
Kolumna autor_id w tabeli ksiazki przechowuje id autora z tabeli autorzy. Klucz obcy formalizuje ten związek i każe Bibliotece pilnować, by każdy autor_id wskazywał na istniejącego autora.
ALTER TABLE ksiazki ADD FOREIGN KEY (autor_id) REFERENCES autorzy(id);Czytamy: "Kolumna autor_id w ksiazki odwołuje się do kolumny id w autorzy".
Integralność referencyjna
Dzięki kluczowi obcemu baza nie pozwoli:
- dodać książki, której
autor_idnie istnieje wautorzy, - usunąć autora, do którego wciąż odwołują się książki (chyba że ustalisz inaczej).
To integralność referencyjna - gwarancja, że odsyłacze w katalogu nigdy nie prowadzą donikąd.
Co zrobić przy usuwaniu?
Możesz określić, co stanie się z książkami, gdy znika ich autor:
ALTER TABLE ksiazki
ADD FOREIGN KEY (autor_id) REFERENCES autorzy(id)
ON DELETE CASCADE;- ON DELETE CASCADE - usunięcie autora automatycznie usuwa jego książki (kaskada).
- ON DELETE SET NULL - usunięcie autora ustawia
autor_idnaNULL(książka zostaje, lecz bez przypisanego autora).
Wybór zależy od tego, jak chcesz dbać o porządek w Bibliotece. Klucze obce to strażnicy spójności - bez nich katalog szybko zapełniłby się martwymi odsyłaczami.
Widzisz błąd w tej lekcji?
Sprawdź się
Odpowiedz na pytania z tej lekcji. Wybierz odpowiedź, a od razu zobaczysz, czy jest poprawna.
1. Do czego służy klucz obcy (FOREIGN KEY)?
2. Co gwarantuje integralność referencyjna wymuszana przez klucz obcy?
To 2 z 4 pytań do tej lekcji. Pozostałe rozwiążesz w grze.
Zadania praktyczne w grze
- Edytor kodu
Dodaj do tabeli ksiazki klucz obcy na kolumnie autor_id, który wskazuje na kolumnę id w tabeli autorzy. Użyj ALTER TABLE.
- Układanie w poziomie
Ułóż w jednej linii poprawny fragment dodający klucz obcy (część REFERENCES):
- Edytor kodu
Dodaj do tabeli wypozyczenia klucz obcy na kolumnie ksiazka_id, który wskazuje na kolumnę id w tabeli ksiazki. Użyj ALTER TABLE.
- Układanie w pionie
Uporządkuj wiersze polecenia dodającego klucz obcy z regułą kasowania kaskadowego (tak jak zapisujemy je w edytorze):
- Edytor kodu
Dodaj do tabeli wypozyczenia klucz obcy na kolumnie czytelnik_id, który wskazuje na kolumnę id w tabeli czytelnicy. Użyj ALTER TABLE.