Reštrikcia v databáze SQL - typ ON DELETE

V minulom diele Reštrikcia v databáze SQL - ON UPDATE sme si ukázali použitie reštrikcií typu ON UPDATE a už by sme mali vedieť meniť identifikátory záznamov tak, aby nedochádzalo k porušeniu integrity databázy.

Úvod

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 2 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. Zlé spracovanie v PHP celkom ľahko naruší integritu databázy. V rámci článku 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 mistnosti WHERE budova_id = 2;
DELETE FROM budovy 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 Miestnosti a zaisťuje, že pokiaľ dôjde k zmazaniu záznamu v nadradenej tabuľke Budovy, dôjde aj k zmazaniu všetkých záznamov príslušných danej budove v tabuľke Miestnosti.

V SQL databáze by sme mali:

CREATE TABLE mistnosti (
   id INT PRIMARY KEY AUTO_INCREMENT,
   budova_id INT NOT NULL
);
ALTER TABLE `mistnosti` ADD FOREIGN KEY (`budova_id`) REFERENCES `budovy` (`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 budovy 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 nad databázu dotaz information_schema:

USE information_schema;

SELECT
   table_name
FROM
   referential_constraints
WHERE
   constraint_schema = 'název_databáze'
   AND referenced_table_name = 'budovy'
   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 Budovy, pokiaľ má priradené nejaké Miestnosti.

Keby sme v SQL mali napr:

CREATE TABLE mistnosti (
   id INT PRIMARY KEY AUTO_INCREMENT,
   budova_id INT NOT NULL
);
ALTER TABLE `mistnosti` ADD FOREIGN KEY (`budova_id`) REFERENCES `budovy` (`id`) ON DELETE RESTRICT;

Pri pokuse o zmazanie Budovy, ktorá obsahuje Miestnosti, bude vyhodená chybová správa o tom, že takúto operáciu nemožno vykonať.

Pri mazaní z SQL:

DELETE FROM budovy WHERE id = 2;

Vyvolá výnimku: Cannot delete or update a parent row: a foreign key constraint fails (testdb. mistnosti, CONSTRAINT mistnosti_budova_id_foreign FOREIGN KEY (budova_id) REFERENCES budovy (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. Pri cudzom kľúči je dôležité na túto možnosť myslieť a nenastavovať mu obmedzenia NOT NULL.

SQL:

CREATE TABLE nabytek (
   id INT PRIMARY KEY AUTO_INCREMENT,
   mistnost_id INT N̶O̶T̶ ̶N̶U̶L̶L̶
);
ALTER TABLE `mistnosti` ADD FOREIGN KEY (`budova_id`) REFERENCES `budovy` (`id`) ON DELETE SET NULL;

Pri zmazaní Miestnosti dôjde k „uvoľneniu nábytku“. Ten zostane zachovaný, ale nebude už 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í

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 au databáz, ktoré toto podporujú. Odporúča sa nahrádzať za SET NULL.