Listopad 10th, 2006MySQL 5.0 díl.1
Od verze 5.0 je možné využívat nových vlastností, které nabízí skutečnou správu dat v databázi.
Jednotlivé vlastnosti rozepíši do vlastních dílů.
V prvním díle se budu věnovat referenčním integritám. Referenční integritu bylo možné využívat již dříve. Respektive od doby, kdy MySQL podporovala typ tabulky InnoDB.
Proč vůbec databáze používá tuto vlastnost? Pokud začnu pracovat s daty v databázi, zjistím, že některé se přímo vážou na jiné. Dříve se používal způsob uchování těchto vazeb v aplikační úrovni. Nevýhody, které to sebou přinášelo je hned několik:- programátor musel stále mít v paměti, která data jsou propojena a podle toho provozoval akční dotazy
- při změně aplikace nad databází se znovu musely tyto vazby naprogramovat
- při provedení UPDATE se museli projít všechny vazby a upravit hodnoty
- a tak dále…
Při použití referenčních integrit v MySQL tyto nepříjemnosti odpadnou.
Jak jsem již psal na začátku, pro využití referenčních integrit musíte použít takový typ tabulky, který danou vlastnost podporuje. Osobně jsem volil nejlepší možnou variantu a to InnoDB.
Lepší než další povídání bude malý příklad:
Mám tabulku uživatelů a tabulku návštěvnosti. Dejme tomu, že u návštěvnosti budu uchovávat informace o počtu návštěv za každý den.
login varchar(10) NOT NULL,
heslo varchar(255) NOT NULL,
jmeno varchar(100) NOT NULL,
prijmeni varchar(100) NOT NULL,
PRIMARY KEY(login)
)ENGINE=InnoDB;
CREATE TABLE uzivatele_navstevnost (
uzivatel varchar(10) NOT NULL,
datum date NOT NULL,
pocet int NOT NULL DEFAULT ‚0‘,
PRIMARY KEY(uzivatel, datum)
)ENGINE=InnoDB;
Vše se zdá být v pořádku. Ale co se stane ve chvíli, kdy některý uživatel změní login? Co se stane ve chvíli, kdy některého uživatele smažu? Nyní mám dvě možnosti, buď v aplikaci budu udržovat vztah uzivatele:login vs. uzivatele_navstevnost:uzivatel nebo použiji referenční integritu.
ADD CONSTRAINT fk_uzivatel_uzivatele
FOREIGN KEY(uzivatel) REFERENCES uzivatele(login)
ON UPDATE CASCADE ON DELETE RESTRICT;
Co jsem v podstatě udělal? Vytvořil jsem cizí klíč v tabulce uzivatele_navstevnost s nazvem fk_uzivatel_uzivatele. Nyní, pokud provedu UPDATE loginu v tabulce uzivatele, změní se mi login uživatele i v tabulce uzivatele_navstevnost. Pokud se pokusím smazat uživatele z tabulky uzivatele, dotaz skončí s chybou. Důvodem je definování DELETE RESTRICT. Zde je jasné, že vlastnost pro DELETE přepíši na CASCADE.
Samotný význam referenčních integrit není usnadnit práci vývojářům při manipulaci s daty, ale udržet data ve správné formě. Vlastnosti pro UPDATE a DELETE pro cizí klíč jsou následující:- CASCADE – provede změny ve sloupci podle změny v referenčním sloupci
- RESTRICT – znemožní upravit či smazat data
- SET NULL – při nastaveni refenečního sloupce na NULL či smazání, nastaví se sloupec cizího klíče na NULL (zde samozřejmě je třeba, aby cizí klíč měl možnost obsahovat NULL)
Na předchozím příkladu jsem ukázal možnost vytvoření referenční integrity mezi dvěmi tabulkami, ale existuje možnost vytvoření takovéto integrity v zájmu jedné tabulky.
Příklad:
Budu mít tabulku zaměstnanců, která navíc obsahuje vztah nadřízený-podřízený.
osobni_cislo int NOT NULL,
jmeno varchar(100) NOT NULL,
prijmeni varchar(100) NOT NULL,
nadrizeny int DEFAULT NULL,
PRIMARY KEY(osobni_cislo),
CONSTRAINT fk_nadrizeny_zamestnanci
FOREIGN KEY(nadrizeny) REFERENCES zamestnanci(osobni_cislo)
ON UPDATE CASCADE ON DELETE SET NULL
)ENGINE=InnoDB;
S definicí referenčních integrit navíc souvisí i správný návrh databáze. K tomuto problému lze říci jen jedno: mít zkušennosti.
V příštím díle se budu věnovat opět typu InnoDB a další vlastnosti a to transakčnímu zpracování.