Kurs MySQL – relacje między tabelami

Za nami już trzy lekcje MySQL 🙂 Jeśli dotarliście do tego miejsca, powinniście już całkiem nieźle radzić sobie z podstawami. Dziś porozmawiamy sobie o tym, czym są relacje. Większość aplikacji internetowych przechowuje zbierane dane w różnych tabelach i operuje na nich za pomocą tzw. kluczy obcych.

Klucze obce (FOREIGN KEY) w MySQL

Klucze obce, czyli tzw FOREIGN KEYS, to nic innego jak powiązania pomiędzy tabelą główną a podrzędną. Klucz obcy w tabeli podrzędnej jest kluczem głównym tabeli nadrzędnej. Są one najczęściej numeryczne ale nic nie stoi na przeszkodzie by klucz główny tabeli był tekstowy. Nie zdarza się to za często, jednak parokrotnie istniała zasadność użycia takiego klucza głównego na tabeli, kiedy istniało realne ryzyko iż zakresu INT zabraknie. Osobiście bardzo łatwo i przyjemnie tworzy się takie relacje za pomocą programu DB Designer. Był to pierwszy tego typu tool jaki poznałam – SQLyog czy MySQL Worbench również posiada tego typu narzędzie.

Tworzenie relacji w MySQL

Spróbujmy na początek stworzyć relację za pomocą kreatora dostęnego w MySQL Workbench. W tym celu wybieramy z paska menu File a następnie New Model:

Następnie wybieramy Add diagram:

Następnie wybieramy z przybornika narzędzie tworzenia tabeli:

Teraz musimy utworzyć dwie tabele plec i imie.

Wyjątkowo nazwy tabeli są w języku polskim, jednak na co dzień w życiu developera używa się języka angielskiego, by opisywać i nazywać wszystko co związane z aplikacją.

Ok, mamy dwie tabele. Zauważcie, że w tabela imie posiada zamiast płci idplec, Jest to kolumna, w której będziemy przechowywać właśnie klucz obcy. Dzięki temu będziemy mogli pobrać nazwę płci z innej tabeli. Na tej samej zasadzie, będziemy mogli pobrać np. nazwę kategorii, w której znajduje się produkt.

Tabele mogą używać jednej z dwóch rodzajów relacji:

• jeden do wielu (1:n) np. relacja pomiędzy imionami i płcią – imię jest męskie bądź żeńskie
•  wiele do wielu (n:m) np. w księgarni jedną książkę mógłby napisać jeden autor, ale ten sam autor mógłby napisać wiele książek i wielu autorów mógłby napisać jedną książkę

Nadajmy więc relację pomiędzy imionami a płcią. W tym celu wybierzmy narzędzie relacji 1:n

Klikamy w idplec w tabeli imie a następnie w idplec w tabeli plec. Naszym oczom ukaże się powiązanie między tabelami, czyli prezentacja relacji:

Zauważcie, ze teraz poniżej PRIMARY stworzył się dodatkowy index fk_imie_plec_idx. Zawsze klucz obcy staje się indeksem – jest to związane z optymalizacją, jednak o tym porozmawiamy w innym artykule. Kliknijmy teraz dwukrotnie w tabelę imie a następnie w zakładkę Foreign Keys:

Po kliknięciu w klucz obcy, widzimy powiązanie oraz skrajnie z prawej strony prezentują nam się opcję „on update” i „on delete”. Służą one do wyzwolenia akcji po zaktualizowaniu bądź usunięciu rekordu. Najczęściej używaną opcją jest cascade, jednak przyjrzyjmy się wszystkim:

ON UPDATE:

• restrict oznacza, iż nie będzie można zaktualizować wiersza z tabeli plec, jeśli posiada powiązanie w tabeli imie
• cascade oznacza, że jeżeli z jakiś względów będziemy potrzebowali zmienić np. długość klucza głównego w tabeli plec (np. poprzedzić ID zerami) , to automatycznie zmiana zostanie naniesiona na kolumnę idplec w tabeli imie
• set null oznacza, że jeżeli zmieni się wartość idplec w tabeli plec, wiersze w tabeli imie, które utracą powiązanie (brak dopasowania idplec), zmienią wartość idplec na NULL.
• no action oznacza, że nie zostanie wykonana żadna akcja

ON DELETE:

• restrict oznacza, iż nie będzie można usunąć wiersza z tabeli plec, jeśli posiada powiązanie w tabeli imie
• cascade oznacza, że wraz z wierszem w tabeli plec usunięte zostaną wszystkie wiersze z tabeli imie, powiązane z tym rekordem
• set null oznacza, że zostanie wprowadzona wartość null w tabeli imie w polu idplec, jeśli powiązany wiersz w tabeli plec zostsanie usunięty
• no action oznacza, że nie zostanie wykonana żadna akcja

Porozmawiajmy jeszcze chwilę o rodzajach powiązań. Użyliśmy wcześniej narzędzia jednak w przyborniku mieliśmy jeszcze łudząco podobne – Linia przerywana reprezentuje brak zdefiniowanego powiązania za pomocą klucza głównego tj. powiązanie nie odbywa się za pomocą klucza głównego w tabeli rodzica.

Relacje za pomocą kwerend MySQL

Naszą gotową relację łatwo możemy wyeksportować jako kwerendy MySQL i przenieść do swojej już istniejącej bazy:

Warto też projektując bazę ją dokumentować w ten sposób. Przy kilku tabelach bowiem łatwo się odnaleźć, ale kiedy nasza aplikacja się rozrasta, czasem ciężko rozwikłać co z czym jest powiązane.

Przyjrzyjmy się jednak naszej kwerendzie tworzącej tabelę imie, bowiem w niej dzieje się najwięcej.

CREATE TABLE IF NOT EXISTS `mydb`.`imie` (
  `idimie` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `imie` VARCHAR(45) NOT NULL,
  `idplec` TINYINT(1) UNSIGNED NOT NULL,
  PRIMARY KEY (`idimie`, `idplec`),
  INDEX `fk_imie_plec_idx` (`idplec` ASC), -- tutaj tworzy się index
  /** poniżej tworzony jest klucz obcy (foreign key) **/
CONSTRAINT `fk_imie_plec`              
    FOREIGN KEY (`idplec`) – wybieramy kolumne, w której będzie przechowywany klucz obcy
    REFERENCES `mydb`.`plec` (`idplec`) -- odwołujemy się do tabeli rodzica i wskazujemy kolumnę, z którą będziemy się wiązać
/** określamy jakie będą wykonywane czynności (bądź ich brak) w przypadku usunięcia/aktualizacji klucza obcego w tabeli rodzica **/
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Dzięki za udział w kolejnej lekcji, jeśli masz chwilę podziel się swoimi wrażeniami i >> zostaw komentarz << w wątku do tego posta.