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. lekcii MySQL/MariaDB , sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

V tutoriáli si preberieme 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 rovno zmazať, alebo musím najprv 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 atď. 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šením zložitosti tejto situácie je použitie reštrikcie ON DELETE CASCADE, ktorá sa definuje na cudzom kľúči v podradenej tabuľke rooms a zaisťuje, že ak príde k zmazaniu záznamu v nadradenej tabuľke buildings, dôjde aj k zmazaniu všetkých záznamov prislúchajúcich k 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 príde k automatickému zmazaniu aj 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é podradené 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, ak 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 miestnostiach mazanej budovy nábytok, o ktorom vieme, že ho nechceme zlikvidovať, ale vysťahovať niekam pred budovu. Pri cudzom kľúči je dôležité na túto možnosť myslieť a nenastavovať mu obmedzenie 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ľúč. Ak by napríklad budova mala skladovú miestnosť s id miestnosti 1, potom nastavenie DEFAULT hodnoty na 1 a nastavenie reštrikcií na SET DEFAULT spôsobí to, že po zmazaní inej miestnosti v danej budove 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ť nadradený záznam, ak existujú záznamy jemu podradené.
SET NULL	Pri mazaní nadradeného záznamu je jemu podradený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 pri databázach, 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 podradený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 podradené. 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. Ak 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 podradené tomuto záznamu.

ON UPDATE RESTRICT (ON UPDATE NO ACTION)

Rozdiel medzi RESTRICT a NO ACTION sme si už povedali v časti o reštrikcii ON DELETE. Aj tu sa riadi zmena nadradeného záznamu tým, či existujú záznamy jemu podradené. Ak 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 podradený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ť. Ak teda nejakým 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 povedali pri reštrikcii ON DELETE, ani ON UPDATE SET DEFAULT nie je vhodné používať vo väčšine bežných prípadov.

Slovo na záver

K napísaniu tejto lekcie ma viedli vlastné skúsenosti z viacerých projektov. Až neuveriteľne často sa stáva, že tak základný stavebný prvok, akým sú reštrikcie, sa v databázach nepoužíva. Možno ani nie preto, že by boli vývojári 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ádzajú a odchádzajú, 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 preč. 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í.