Cudzie kľúče (foreign keys) v databáze MySQL

Published:

Add / read comments

Tak sa skúsime dnes pozrieť pod kapotu každej aplikácie, do motora. No a motorom nemôže byť nič iné ako databáza, kam všetky informácie, ktoré nám užívatelia poskytli ukladáme.

Ukážeme si teda vytvorenie a prepojenie dvoch tabuliek v MySQL databáze. Najprv vytvoríme tabuľku osoby:


 DROP TABLE IF EXISTS `osoby`;

 CREATE TABLE IF NOT EXISTS `osoby` (
  `osoby_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `osoby_titul_pred` INT(10) UNSIGNED DEFAULT NULL,
  `osoby_meno` VARCHAR(255) COLLATE utf8_slovak_ci DEFAULT NULL,
  `osoby_priezvisko` VARCHAR(255) COLLATE utf8_slovak_ci DEFAULT NULL,
  PRIMARY KEY (`osoby_id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci AUTO_INCREMENT=1;

stĺpec "osoby_titul_pred" obsahuje identifikátor titulu v inej tabuľke. Teraz vytvoríme tabuľku kde budú uložené tituly:


 DROP TABLE IF EXISTS `titul_pred`;

 CREATE TABLE IF NOT EXISTS `titul_pred` (
  `titul_pred_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `titul_pred_nazov` VARCHAR(30) COLLATE utf8_slovak_ci DEFAULT NULL,
  PRIMARY KEY (`titul_pred_id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci AUTO_INCREMENT=1;

a teraz spomenuté dve tabuľky prepojíme navzájom cez cudzí kľúč pridaním obmedzenia (constraint) na tabuľku "osoby":


 ALTER TABLE `osoby`
  ADD CONSTRAINT `osoby_fk_titul_pred` 
  FOREIGN KEY (`osoby_titul_pred`) 
  REFERENCES `titul_pred` (`titul_pred_id`) 
  ON UPDATE CASCADE;
	

ak by sme chceli cudzí kľúč (foreign key) odstrániť, stačí napísať:


 ALTER TABLE `osoby` DROP FOREIGN KEY `osoby_fk_titul_pred`; 
 

a cudzí kľúč sa odstráni.

Čo je veľmi dôležité spomenúť je to, ž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.

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


 INSERT INTO `titul_pred` (`titul_pred_id`, `titul_pred_nazov`) 
 VALUES (1, 'Bc.'),(2, 'Mgr.'),(3, 'Ing.');

A teraz nejaké osoby:


 INSERT INTO `osoby` (`osoby_id`, `osoby_titul_pred`, `osoby_meno`, `osoby_priezvisko`) 
 VALUES (1, 3, 'david', 'dubak'), (2, NULL, 'peter', 'mrkvička');

Aký je rozdiel medzi prvou a druhou vkladanou osobou? Iba taký, že prvá odkazuje do referenčného číselníka titulov.

No a čo cudzí kľúč spôsobí? Nedovolí odstrániť záznam v tabuľke "titul_pred", ktorý sa odkazuje na tabuľku "osoby". Respektíve ak záznam v tabuľke "osoby" používa odkaz na tabuľku "titul_pred", obmedzenie (constraint) nedovolí záznam v tabuľke "titul_pred" zmazať, lebo by sa narušila konzistencia databázy. Záznam v osobách by ukazoval na neexistujúci záznam do tabuľky "titul_pred". Čiže ak skúsim vymazať inženiersky titul, ktorý používa osoba:


 DELETE FROM `titul_pred` WHERE `titul_pred`.`titul_pred_id` = 3;

tak systém oznámi chybu:

Cannot delete or update a parent row: a foreign key constraint fails (`osoby`, CONSTRAINT `osoby_fk_titul_pred` FOREIGN KEY (`osoby_titul_pred`) REFERENCES `titul_pred` (`titul_pred_id`) ON UPDATE CASCADE)

 Cannot DELETE OR UPDATE a parent ROW: a FOREIGN KEY CONSTRAINT fails 
 (`osoby`, CONSTRAINT `osoby_fk_titul_pred` FOREIGN KEY (`osoby_titul_pred`) REFERENCES `titul_pred` (`titul_pred_id`) ON UPDATE CASCADE)

Ak by som sa pokúsil vymazať titul, ktorý sa v tabuľke "osoby" nepoužíva, nebol by to žiadny problém.

No a prečo sa teda tituly vyčleňujú z pôvodnej tabuľky do vlastnej? Tu sa dostávame k pojmu relačná databáza. Tabuľky databázy vykazujú niekoľko vlastností:

  • každý riadok je unikátny

  • nezáleží na poradí stĺpcov

  • nezáleží na poradí riadkov

  • údaje v jednom stĺpci majú ten istý typ

  • každý stĺpec má unikátny názov

Unikátny názov každho stĺpca sa nazýva primárny kľúč (primary key) a základom relačného modelu je pojem relácia. Databáza je teda množina relačných tabuliek, ktoré sú medzi sebou v nejakej relácii. Rozpoznávame 3 relácie medzi dvomi tabuľkami:

  • 1:1

  • 1:N

  • M:N

Čiže tabuľky "osoby" a "titul_pred" sú v tomto prípade vo vzájomnom relačnom vzťahu 1:N. To znamená, že jedna osoba odkazuje na jeden titul, ale jeden titul môže byť použitý u viacerých osôb.

Toto však nič nehovorí o tom, prečo je vhodné vyčleniť tituly do vlastnej tabuľky a prepojiť reláciou. Prečo sa teda nenechajú tituly v pôvodnej tabuľke ako textový stĺpec? Lebo vyseparovaním sa minimalizuje redundancia (opakovanie) dát. Ľahko by sa stalo, že mnoho osôb by bolo inženierov a táto informácia by sa bola v tabuľke opakovala. A keby chceme potom inženiersky titul premenovať na iný, bol by problém (síce ono by sa to dalo, ale načo si komplikovať život).

Proces pri ktorom sa eliminujú duplicity v databáze sa nazýva normalizácia (áno, aj v 70-tych rokoch bežala v bývalom Československu normalizácia, ale to nebola tá databázová). Normalizácia je proces eliminovania duplicitných údajov v relačných databázach. Správne normalizované databázy majú návrh, ktorý reflektuje pravé závislosti medzi sledovanými položkami a umožňuje rýchlu zmenu údajov bez rizika vzniku nekonzistencie.

Pri návrhu relačnej databázy sa dodržiavajú teda pravidlá, ktoré sa nazývajú normálové formy. Je ich šesť.

  • Prvá normálna forma (1NF) je, keď všetky atribúty sú atomické, t. j. ďalej nedeliteľné. Jeden stĺpec nesmie obsahovať viac druhov údajov, musí obsahovať skalárnu hodnotu.

  • Druhá normálna forma (2NF) je, keď spĺňa podmienky 1NF a každý nekľúčový atribút musí byť úplne funkčne závislý na primárnom kľúči (na celom kľúči, nielen na jeho podmnožine).

  • Tretia normálna forma (3NF) je, keď spĺňa podmienku 2NF a všetky jej atribúty nie sú tranzitívne závislé na primárnom kľúči.

  • Štvrtá a piata normálna forma sa zaoberajú konkrétne reprezentáciou vzťahov 1:N a M:N.

  • Šiesta NF sa vzťahuje iba na temporálne databázy.
Tranzitívna závislosť znamená, že hodnoty atribútov nezávisia iba na primárnom kľúči, ale aj na hodnotách iných atribútoch, ktoré sú zase závislé na primárnom kľúči.

Aj vám to pripadá také divoké ako mne? Toto bolo naozaj len také priblíženie v kocke. Po internete sa povaľuje veľa článkov a v kníhkupectvách veľa kníh aj s peknými obrázkami, kde sa normalizácia databáz podrobne vysvetľuje. Tak to je na dnes všetko.

Published:

Add / read comments

FIND ME

Share, follow or connect with me.