Lekce 23 - Restrikce v databázi SQL - ON DELETE a ON UPDATE

V předešlém cvičení, Řešené úlohy k 21.-22. lekci MySQL/MariaDB , jsme si procvičili nabyté zkušenosti z předchozích lekcí.

Možná s databázemi teprve začínáte, možná je používáte již řadu měsíců či let a možná v rámci aplikací pravidelně řešíte otázky typu: "Chci smazat tento záznam, s čím vším je propojen?", "Jak smazání ovlivní provázanost databáze?", "Mohu záznam smazat rovnou, nebo musím předem odebrat to a tamto?", a "Musím na to pořád myslet?“. Nemusíte! Ukážeme si, jak lze pomocí restrikcí jednoduše databázi přimět, aby tyto otázky řešila za nás. A nebojte se, není to žádná věda.

ON DELETE CASCADE

Představme si vzorovou situaci, kdy máme v databázi 2 tabulky - tabulku s budovami a tabulku s místnostmi`:

• Každá budova má libovolný počet místností.
• Každá místnost patří jedné budově - místnost bez budovy nemůže existovat.
• Přiřazení místnosti k budově se provádí pomocí definice cizích klíčů.
• Tabulky jsou ve vztahu 1:N.
• Při smazání místnosti se nic neděje.
• Při smazání budovy musí dojít i ke smazání místností.

Pro funkčnost příkladu je nutné smazat všechny místnosti patřící do dané budovy a až poté smazat samotnou budovu. Na toto je nutno vždy myslet a hlídat to. V rámci lekce jde o jednoduchý příklad, ovšem v reálném projektu může být provázání jednotlivých záznamů mnohem komplexnější. Např. sešrotováním auta dojde k odpisu nejen auta jako takového, ale i jeho součástí – motoru, převodovky, kol, aj. Motor může dále obsahovat odkazy na další součástky, které musí být smazány, atd.

Mazání bez použití restrikcí

Mazání bez restrikcí bychom napsali nějak takto. V SQL databázi bychom mohli mít:

DELETE FROM `mistnosti` WHERE `budova_id` = 2;
DELETE FROM `budovy` WHERE `id` = 2;

Použití restrikce ON DELETE CASCADE

S komplexností provázání však roste množství vynaloženého úsilí na vytváření dalších a dalších mazacích dotazů na databázi. Řešení složitosti této situace je použití restrikce ON DELETE CASCADE, která se definuje na cizím klíči v podřízené tabulce mistnosti a zajišťuje, že pokud dojde ke smazání záznamu v nadřazené tabulce budovy, dojde i ke smazání všech záznamů příslušných dané budově v tabulce mistnosti.

V SQL databázi bychom měli:

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 nyní zajištěno, že smazáním budovy dojde k automatickému smazání všech příslušných místností.

Můžeme si vyzkoušet v SQL:

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

Zjištění restrikce

V databázi lze pro požadovanou tabulku jednoduše zjistit, které podřízené tabulky mají tuto restrikci nastavenou.

Pro zjištění restrikce vložíme SQL nad databázi dotaz information_schema:

SELECT
   table_name
FROM
   information_schema.referential_constraints
WHERE
   constraint_schema = 'název_databáze'
   AND delete_rule = 'CASCADE';

ON DELETE RESTRICT (ON DELETE NO ACTION)

V MySQL/MariaDB jde o ekvivalentní restrikce. V jiných databázích (PostgreSQL, SQLite, …) vycházejících z SQL 2003 standardu je mezi nimi rozdíl v tom, že se RESTRICT kontroluje před jakoukoliv další operací, a NO ACTION se provádí až po provedení operací, kterými mohou být např. spouštěče. Databáze Oracle RESTRICT nepoužívá.

U MySQL/MariaDB je RESTRICT ve skutečnosti výchozím nastavením pro definici cizího klíče bez explicitního zadání restrikcí.

Nastavením této restrikce na cizí klíč je zakázáno smazání záznamu nadřazené tabulky budovy, pokud má přiřazené nějaké mistnosti.

Kdybychom v SQL měli např:

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;

Při pokusu o smazání budovy, která obsahuje mistnosti bude vyhozena chybová zpráva o tom, že takovouto operaci nelze provést.

Při mazání z SQL:

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

Vyvolá výjimku: 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 případě, že podřízené záznamy smazat nechceme, je možné nastavit cizí klíč při smazání nadřazeného záznamu na NULL. Udělali bychom to například v případě, že máme v pokojích mazané budovy nábytek, o kterém víme, že ho nechceme zničit, ale vystěhovat někam před budovu. U cizího klíče je důležité na tuto možnost myslet a nenastavovat mu omezení NOT NULL:

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

Při smazání mistnosti dojde k "uvolnění nábytku". Ten zůstane zachován, ale již nebude přiřazen k žádné místnosti.

ON DELETE SET DEFAULT

Výchozí hodnota záleží na nastavení daného sloupce pro daný cizí klíč. Pokud by například budova měla skladovou místnost s id místnosti 1, pak nastavením DEFAULT hodnoty na 1 a nastavením restrikcí na SET DEFAULT způsobí to, že po smazání jiné místnosti dané budovy se její nábytek automaticky přesune do jejího skladu.

Ne všechny databázové enginy SET DEFAULT podporují! Například často používaný InnoDB, nebo méně známý NDB zadání těchto restrikcí zpracují, ale ignorují je. Jsou nahrazeny za RESTRICT.

Tato restrikce má ale svá omezení (dle popsaného příkladu):

• Při smazání místnosti v jakékoliv budově se převede veškerý nábytek do místnosti s id = 1. Nelze tedy nastavit něco jako skladovou místnost pro každou budovu zvlášť.
• Při vytváření tohoto cizího klíče již musí existovat záznam s id = 1 v tabulce místností.

Shrnutí ON DELETE restrikcí

CASCADE	Probublání informace o smazání nadřízeného záznamu a smazání i záznamů jemu podřízených.
RESTRICT/NO ACTION	Je zakázáno smazat nadřízený záznam, pokud existují záznamy jemu podřízené.
SET NULL	Při mazání nadřazeného záznamu je jemu podřízeným záznamům odebráno propojení na tento záznam a samotný záznam je smazán.
SET DEFAULT	Tuto restrikci není doporučováno používat mimo velice specifické případy a u databází, které toto podporují. Doporučuje se nahrazovat za SET NULL.

ON UPDATE vs ON DELETE

Restrikce ON DELETE za nás hlídá, co se děje s podřízenými záznamy v případě, kdy dojde ke smazání záznamu jim nadřazenému. SQL stroj hlídá cizí klíče a v případě existence odkazu na právě mazaný primární klíč provede operaci dle nastavení restrikce. ON UPDATE je svou funkčností velice podobný, jen s tím rozdílem, že nehlídá dotazy na mazání primárních klíčů, ale hlídá dotazy na jejich modifikaci.

ON UPDATE CASCADE

Podobně jako u restrikce ON DELETE, kaskáda zde znamená probublání změny (v tomto případě například přečíslování primárního klíče) nadřazeného záznamu na záznamy podřízené. V praxi to vypadá například tak, že pokud záznamu, na který se odkazují záznamy jiných tabulek změníme primární klíč, je tento primární klíč změněn i u všech záznamů v odkazujících tabulkách:

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 UPDATE CASCADE;

Doposud jsem se v praxi nesetkal s případem, kdy by bylo třeba měnit identifikátor databázového záznamu. Pokud k tomu ale nějaký důvod existuje, změníme např. id budovy ze 2 na 5:

UPDATE `budovy` SET `id` = 5 WHERE `id` = 2;

Změna se aplikuje i na všechny místnosti náležící budově s id 2, čili na záznamy podřízené tomuto záznamu.

ON UPDATE RESTRICT (ON UPDATE NO ACTION)

Rozdíl mezi RESTRICT a NO ACTION jsme si již popsali v části o restrikci ON DELETE. I zde se řídí změna nadřazeného záznamu tím, zda existují záznamy jemu podřízené. Pokud např. existují mistnosti přiřazené budově, tak této budově nelze upravit její primární klíč:

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 UPDATE RESTRICT;

ON UPDATE SET NULL

Při změně primárního klíče nadřazeného záznamu dojde k uvolnění jemu podřízených záznamů – hodnota cizího klíče je nastavena na NULL. Opět jako u ON DELETE je i zde potřeba na toto myslet při definici cizího klíče a nezapomenout nezadávat mu NOT NULL vlastnost. Pokud tedy nějaké mistnosti změníme primární klíč, dojde k vystěhování veškerého nábytku na ulici:

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

ON UPDATE SET DEFAULT

Stejně jako jsme si popsali u restrikce ON DELETE, ani ON UPDATE SET DEFAULT není vhodné pro většinu běžných případů.

Slovo závěrem

K napsání této lekce mě vedly vlastní zkušenosti z vícero projektů. Až těžko uvěřitelně často se stává, že tak základní stavební prvek, jakým jsou restrikce se v databázích nepoužívají. Možná ani ne tak proto, že by vývojáři byli líní, ale jak to určitě taky znáte, prostě není čas, a přesune se to "na později". A nebo se s tím u projektu začne, vývojáři přichází a odchází, ztrácí se povědomí o vazbách a procesech a přehled o tom, jak je databáze postavená. Končí to tak, že se databáze začne používat pouze jako odkladiště hromady dat, která nějak funguje, ačkoli integrita je dávno ta tam. Samozřejmě to není problémem všech společností, ale setkávám se s tím celkem pravidelně. Až se pak jednou to kolečko poláme.

Třeba takovou maličkostí, jako je odkaz na neexistující nadřazený záznam, který přitom ani není důležitý a vlastně jej v danou chvíli nechceme. Požadovaný záznam byl dávno uvolněn a nám nastává opravdové peklo. Projít celou, mnohdy několika gigabajtovou databázi, dohledat kolize, opravit záznamy, nastavit restrikce, zabere i týdny tvrdé práce.

V příloze následující lekce naleznete cheatsheet s popisem jednotlivých restrikcí (mimo ně tak často využívaný SET DEFAULT), včetně ukázky na jednoduchých příkladech.

V následujícím kvízu, Kvíz - Cizí klíče a restrikce v MySQL, si vyzkoušíme nabyté zkušenosti z předchozích lekcí.