Cudzie kľúče (foreign keys) v databáze MySQL - časť 2.

Published:

Add / read comments

V minulom článku sa rozoberalo použitie cudzích kľúčov v MySQL v prípade, ak je potrebné využiť referenčnú tabuľku (číselník) a zabezpečiť, aby nebol zmazaný záznam, na ktorý sa odkazujú iné tabuľky. Teraz si ukážeme iný veľmi používaný a často potrebný prípad a to keď k objektu potrebujeme priradiť viacero nejakých objektov. Napríklad jedna osoba môže mať viacero áut, ktoré uložíme do druhej tabuľky a prepojíme pomocou cudzieho kľúča.

Najprv vytvoríme tabuľku pre autá:


 DROP TABLE IF EXISTS `auta`;
 
 CREATE TABLE IF NOT EXISTS `auta` (
   `auta_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `auta_vlastnik` INT(10) UNSIGNED NOT NULL,
   `auta_typ` INT(10) UNSIGNED NOT NULL,
   PRIMARY KEY (`auta_id`),
   KEY `auta_vlastnik` (`auta_vlastnik`),
   KEY `auta_typ` (`auta_typ`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci;

A teraz prečo stĺpec "auta_typ" je dátového typu INT a nieje to reťazec? Ako tam potom vložíme o aký typ auta ide? Tu práve prichádza na rad normalizácia databázy. Ak by sa do stĺpca vkladali rovno typy áut, vznikali by duplicity. Napr. ak by mala jedna osoba škodovku a iná tiež, boli by v stĺpci dve rovnaké hodnoty. A preto je potrebné vytvoriť ďalšiu tabuľku a tieto dáta vyseparovať:


 DROP TABLE IF EXISTS `ciselnik_aut`;
 
 CREATE TABLE IF NOT EXISTS `ciselnik_aut` (
   `ciselnik_aut_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `ciselnik_aut_nazov_auta` VARCHAR(255) COLLATE utf8_slovak_ci DEFAULT NULL,
   PRIMARY KEY (`ciselnik_aut_id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci;

Teraz sa pridajú na tabuľku "auta" obmedzenia (constraints), ktoré pomocou cudzích kľúčov prepoja tabuľky "auta", "osoby" a "cislenik_aut":


 ALTER TABLE `auta`
  ADD CONSTRAINT `auta_fk_osoby` FOREIGN KEY (`auta_vlastnik`) 
  REFERENCES `osoby` (`osoby_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `auta_fk_ciselnik_aut` FOREIGN KEY (`auta_typ`) 
  REFERENCES `ciselnik_aut` (`ciselnik_aut_id`) ON UPDATE CASCADE;
		

Teraz naplníme tabuľky nejakými dátami. Najprv referenčný číselník áut:


 INSERT INTO `ciselnik_aut` (`ciselnik_aut_id`, `ciselnik_aut_nazov_auta`) 
 VALUES (1, 'BMW'), (2, 'Ford');

A teraz tabuľku "auta":


 INSERT INTO `auta` (`auta_id`, `auta_vlastnik`, `auta_typ`) 
 VALUES (1, 2, 2), (2, 1, 1);

Čiže teraz je v tabuľke "auta" informácia o tom, že vlastník auta s ID = 2 má typ auta 2 (prvý záznam). Druhý záznam ukazuje, že vlastník auta s ID = 1 má typ auta 1 (to je bavorák :-) ).

No a teraz späť k cudzím kľúčom. Cudzí kľúč "auta_fk_ciselnik_aut" je presne to isté ako s titulmi v predchádzajúcom článku.

Zaujímavejší je v tomto prípade ten druhý (auta_fk_osoby). Ak by cudzí kľúč nebol prítomný vôbec, tak zmazaním osoby v tabuľke "osoby" by v tabuľke "auta" ostali "visieť" autá, ktoré by nemali svojich majiteľov. Narušila by sa tak konzistencia databázy.

Cudzí kľúč "auta_fk_osoby" hovorí o tom, že ak chcem zmazať osobu, tak zmaže aj všetky autá, ktoré jej patria. Toto sa zabezpečí práve klauzulou ON DELETE CASCADE. Ak by tam nebola, tak potom po zmazaní osoby:


 DELETE FROM `osoby` WHERE `osoby`.`osoby_id` = 1

MySQL databáza oznámi chybu:

Cannot DELETE OR UPDATE a parent ROW: a FOREIGN KEY CONSTRAINT fails 
(`auta`, CONSTRAINT `auta_fk_osoby` FOREIGN KEY (`auta_vlastnik`) REFERENCES `osoby` (`osoby_id`))

V prípade ak je kaskáda prítomná, tak vymazaním osoby sa vymažú aj všetky autá, ktoré osobe patria.

Na záver je možno dobré len opäť pripomenúť že východiskovým úložným mechanizmom MySQL databázy je MyISAM mechanizmus. Tento však nepodporuje transakcie a nevynucuje si cudzie kľúče. Preto je pri vytváraní tabuliek zvolený úložný mechanizmus InnoDB.

Published:

Add / read comments

FIND ME

Share, follow or connect with me.