23. diel - Reštrikcie v databáze SQL - ON DELETE a ON UPDATE

V predchádzajúcom cvičení, Riešené úlohy k 21.-22. lekciu MySQL/MariaDB , sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

V tutoriáli si probereme reštrikcie, ktoré sú významným pomocníkom pre automatizáciu procesov nad databázou. Ukážeme si, ako fungujú a ako ich používať.

Možno s databázami ešte len začínate, možno ich používate už niekoľko mesiacov či rokov a možno v rámci aplikácií pravidelne riešite otázky typu: "Chcem zmazať tento záznam, s čím všetkým je prepojený?", "Ako zmazanie ovplyvní previazanosť databázy?", "Môžem záznam zmazať rovno, alebo musím vopred odobrať to a tamto?", a "Musím na to stále myslieť?“. Nemusíte! Ukážeme si, ako možno pomocou reštrikcií jednoducho databázu prinútiť, aby tieto otázky riešila za nás. A nebojte sa, nie je to žiadna veda.

ON DELETE CASCADE

Predstavme si vzorovú situáciu, kedy máme v databáze dve tabuľky - tabuľku budovy a tabuľku miestnosti:

• Každá budova má ľubovoľný počet miestností.
• Každá miestnosť patrí jednej budove - miestnosť bez budovy nemôže existovať.
• Priradenie miestnosti k budove sa vykonáva pomocou definície cudzích kľúčov.
• Tabuľky sú vo vzťahu 1:N.
• Pri zmazaní miestnosti sa nič nedeje.
• Pri zmazaní budovy musí dôjsť aj k zmazaniu miestností.

Pre funkčnosť príkladu je nutné zmazať všetky miestnosti patriace do budovy a až potom zmazať samotnú budovu. Na toto treba vždy myslieť a strážiť to. V rámci lekcie ide o jednoduchý príklad, avšak v reálnom projekte môže byť previazanie jednotlivých záznamov oveľa komplexnejšie. Napr. zošrotovaním auta dôjde k odpisu nielen auta ako takého, ale aj jeho súčastí – motora, prevodovky, kolies, ai. Motor môže ďalej obsahovať odkazy na ďalšie súčiastky, ktoré musia byť zmazané, atď.

Mazanie bez použitia reštrikcií

Mazanie bez reštrikcií by sme napísali nejako takto. V SQL databáze by sme mohli mať:

DELETE FROM `rooms` WHERE `building_id` = 2;
DELETE FROM `buildings` WHERE `id` = 2;

Použitie reštrikcie ON DELETE CASCADE

S komplexnosťou previazania však rastie množstvo vynaloženého úsilia na vytváranie ďalších a ďalších mazacích dotazov na databázu. Riešenie zložitosti tejto situácie je použitie reštrikcie ON DELETE CASCADE, ktorá sa definuje na cudzom kľúči v podriadenej tabuľke rooms a zaisťuje, že pokiaľ dôjde k zmazaniu záznamu v nadradenej tabuľke buildings, dôjde aj k zmazaniu všetkých záznamov príslušných danej budove v tabuľke rooms.

V SQL databáze by sme mali:

CREATE TABLE `rooms` (
   `id` INT PRIMARY KEY AUTO_INCREMENT,
   `building_id` INT NOT NULL
);
ALTER TABLE `rooms` ADD FOREIGN KEY (`building_id`) REFERENCES `buildings` (`id`) ON DELETE CASCADE;

Týmto je teraz zaistené, že zmazaním budovy dôjde k automatickému zmazaniu všetkých príslušných miestností.

Môžeme si vyskúšať v SQL:

DELETE FROM `buildings` WHERE `id` = 2;

Zistenie reštrikcie

V databáze je možné pre požadovanú tabuľku jednoducho zistiť, ktoré podriadené tabuľky majú túto reštrikciu nastavenú.

Pre zistenie reštrikcie vložíme SQL dotaz nad databázu information_schema:

SELECT
   table_name
FROM
   information_schema.referential_constraints
WHERE
   constraint_schema = 'database_name'
   AND delete_rule = 'CASCADE';

ON DELETE RESTRICT (ON DELETE NO ACTION)

V MySQL/MariaDB ide o ekvivalentné reštrikcie. V iných databázach (PostgreSQL, SQLite, …) vychádzajúcich z SQL 2003 štandardu je medzi nimi rozdiel v tom, že sa RESTRICT kontroluje pred akoukoľvek ďalšou operáciou, a NO ACTION sa vykonáva až po vykonaní operácií, ktorými môžu byť napr. spúšťače. Databáza Oracle RESTRICT nepoužíva.

Pri MySQL/MariaDB je RESTRICT v skutočnosti východiskovým nastavením pre definíciu cudzieho kľúča bez explicitného zadania reštrikcií.

Nastavením tejto reštrikcie na cudzí kľúč je zakázané zmazanie záznamu nadradenej tabuľky buildings, pokiaľ má priradené nejaké miestnosti.

Keby sme v SQL mali napr:

CREATE TABLE `rooms` (
   `id` INT PRIMARY KEY AUTO_INCREMENT,
   `building_id` INT NOT NULL
);
ALTER TABLE `rooms` ADD FOREIGN KEY (`building_id`) REFERENCES `buildings` (`id`) ON DELETE RESTRICT;

Pri pokuse o zmazanie budovy, ktorá obsahuje miestnosti sa vyhodí chybové hlásenie o tom, že takúto operáciu nemožno vykonať.

Pri mazaní z SQL:

DELETE FROM `buildings` WHERE `id` = 2;

Vyvolá výnimku: Cannot delete or update a parent row: a foreign key constraint fails (testdb.rooms, CONSTRAINT rooms_building_id_foreign FOREIGN KEY (building_id) REFERENCES `buildings (id))

ON DELETE SET NULL

V prípade, že podriadené záznamy zmazať nechceme, je možné nastaviť cudzí kľúč pri zmazaní nadradeného záznamu na NULL. Urobili by sme to napríklad v prípade, že máme v izbách mazané budovy nábytok, o ktorom vieme, že ho nechceme zničiť, ale vysťahovať niekam pred budovu. U cudzieho kľúča je dôležité na túto možnosť myslieť a nenastavovať mu obmedzenia NOT NULL:

CREATE TABLE `furniture` (
   `id` INT PRIMARY KEY AUTO_INCREMENT,
   `room_id` INT N̶O̶T̶ ̶N̶U̶L̶L̶
);
ALTER TABLE `furniture` ADD FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE SET NULL;

Pri zmazaní miestnosti dôjde k "uvoľneniu nábytku". Ten zostane zachovaný, ale už nebude priradený k žiadnej miestnosti.

ON DELETE SET DEFAULT

Predvolená hodnota záleží na nastavení daného stĺpca pre daný cudzí kľúč. Pokiaľ by napríklad budova mala skladovú miestnosť s id miestnosti 1, potom nastavením DEFAULT hodnoty na 1 a nastavením reštrikcií na SET DEFAULT spôsobí to, že po zmazaní inej miestnosti danej budovy sa jej nábytok automaticky presunie do jej skladu.

Nie všetky databázové enginy SET DEFAULT podporujú! Napríklad často používaný InnoDB, alebo menej známy NDB zadanie týchto reštrikcií spracujú, ale ignorujú ich. Sú nahradené za RESTRICT.

Táto reštrikcia má ale svoje obmedzenia (podľa popísaného príkladu):

• Pri zmazaní miestnosti v akejkoľvek budove sa prevedie všetok nábytok do miestnosti s id = 1. Nie je teda možné nastaviť niečo ako skladovú miestnosť pre každú budovu zvlášť.
• Pri vytváraní tohto cudzieho kľúča už musí existovať záznam s id = 1 v tabuľke miestností.

Zhrnutie ON DELETE reštrikcií

Zhrnieme si ON DELETE reštrikcie:

CASCADE	Prebublanie informácie o zmazaní nadriadeného záznamu a zmazaní i záznamov jemu podriadených.
RESTRICT/NO ACTION	Je zakázané zmazať nadriadený záznam, pokiaľ existujú záznamy jemu podriadené.
SET NULL	Pri mazaní nadradeného záznamu je mu podriadeným záznamom odobrané prepojenie na tento záznam a samotný záznam je zmazaný.
SET DEFAULT	Túto reštrikciu nie je odporúčané používať mimo veľmi špecifických prípadov a u databáz, ktoré toto podporujú. Odporúča sa nahrádzať za SET NULL.

ON UPDATE vs. ON DELETE

Reštrikcia ON DELETE za nás stráži, čo sa deje s podriadenými záznamami v prípade, keď dôjde k zmazaniu záznamu im nadradenému. SQL stroj stráži cudzie kľúče a v prípade existencie odkazu na práve mazaný primárny kľúč vykoná operáciu podľa nastavenia reštrikcie. ON UPDATE je svojou funkčnosťou veľmi podobný, len s tým rozdielom, že nestráži dotazy na mazanie primárnych kľúčov, ale stráži dotazy na ich modifikáciu.

ON UPDATE CASCADE

Podobne ako pri reštrikcii ON DELETE, kaskáda tu znamená prebublanie zmeny (v tomto prípade napríklad prečíslovanie primárneho kľúča) nadradeného záznamu na záznamy podriadené. V praxi to vyzerá napríklad tak, že ak záznamu, na ktorý sa odkazujú záznamy iných tabuliek zmeníme primárny kľúč, je tento primárny kľúč zmenený aj pri všetkých záznamoch v odkazujúcich tabuľkách:

CREATE TABLE `rooms` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `building_id` INT NOT NULL
);
ALTER TABLE `rooms` ADD FOREIGN KEY (`building_id`) REFERENCES `buildings` (`id`) ON UPDATE CASCADE;

Doteraz som sa v praxi nestretol s prípadom, kedy by bolo treba meniť identifikátor databázového záznamu. Pokiaľ k tomu ale nejaký dôvod existuje, zmeníme napr. building_id z 2 na 5:

UPDATE `buildings` SET `id` = 5 WHERE `id` = 2

Zmena sa aplikuje aj na všetky miestnosti patriace budove 2, čiže na záznamy podriadené tomuto záznamu.

ON UPDATE RESTRICT (ON UPDATE NO ACTION)

Rozdiel medzi RESTRICT a NO ACTION sme si už popísali v časti o reštrikcii ON DELETE. Aj tu sa riadi zmena nadradeného záznamu tým, či existujú záznamy jemu podriadené. Pokiaľ napr. existujú rooms priradené budove, tak tejto budove nemožno upraviť jej primárny kľúč:

CREATE TABLE `rooms` (
   `id` INT PRIMARY KEY AUTO_INCREMENT,
   `building_id` INT NOT NULL
);
ALTER TABLE `rooms` ADD FOREIGN KEY (`building_id`) REFERENCES `buildings` (`id`) ON UPDATE RESTRICT;

ON UPDATE SET NULL

Pri zmene primárneho kľúča nadradeného záznamu dôjde k uvoľneniu jemu podriadených záznamov – hodnota cudzieho kľúča je nastavená na NULL. Opäť ako pri ON DELETE je aj tu potrebné na toto myslieť pri definícii cudzieho kľúča a nezabudnúť nezadávať mu NOT NULL vlastnosť. Pokiaľ teda nejaké rooms zmeníme primárny kľúč, dôjde k vysťahovaniu všetkého nábytku na ulici:

CREATE TABLE `rooms` (
   `id` INT PRIMARY KEY AUTO_INCREMENT,
   `building_id` INT NOT NULL
);
CREATE TABLE `furniture` (
   `id` INT PRIMARY KEY AUTO_INCREMENT,
   `room_id` INT N̶O̶T̶ ̶N̶U̶L̶L̶
);
ALTER TABLE `furniture` ADD FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON UPDATE SET NULL;

ON UPDATE SET DEFAULT

Rovnako ako sme si popísali pri reštrikcii ON DELETE, ani ON UPDATE SET DEFAULT nie je vhodné pre väčšinu bežných prípadov.

Slovo na záver

K napísaniu tejto lekcie ma viedli vlastné skúsenosti z viacerých projektov. Až ťažko uveriteľne často sa stáva, že tak základný stavebný prvok, akým sú reštrikcie sa v databázach nepoužívajú. Možno ani nie tak preto, že by vývojári boli leniví, ale ako to určite tiež poznáte, jednoducho nie je čas, a presunie sa to "na neskôr". Alebo sa s tým pri projekte začne, vývojári prichádza a odchádza, stráca sa povedomie o väzbách a procesoch a prehľad o tom, ako je databáza postavená. Končí to tak, že sa databáza začne používať iba ako odkladisko hromady dát, ktorá nejako funguje, hoci integrita je dávno tá tam. Samozrejme to nie je problémom všetkých spoločností, ale stretávam sa s tým celkom pravidelne. Až sa potom raz to koliesko poláme.

Napríklad takou maličkosťou, ako je odkaz na neexistujúci nadradený záznam, ktorý pritom ani nie je dôležitý a vlastne ho v danej chvíli nechceme. Požadovaný záznam bol dávno uvoľnený a nám nastáva skutočné peklo. Prejsť celú, mnohokrát niekoľko gigabajtovú databázu, dohľadať kolízie, opraviť záznamy, nastaviť reštrikcie, zaberie aj týždne tvrdej práce.

V nasledujúcom kvíze, Kvíz - Cudzie kľúče a reštrikcie v MySQL, si vyskúšame nadobudnuté skúsenosti z predchádzajúcich lekcií.