Lekce 10 - PostgreSQL - Dotazy přes více tabulek - Dokončení

V minulé lekci, PostgreSQL - Dotazy přes více tabulek (JOIN), jsme si ukázali dotazy přes více tabulek pomocí příkazu JOIN.

V dnešním PostgreSQL tutoriálu si vysvětlíme rozdíly mezi INNER JOIN a OUTER JOIN a seznámíme se s cizím klíčem FOREIGN KEY.

INNER JOIN a OUTER JOIN

INNER(vnitřní) a OUTER (vnější) JOIN jsou 2 typy příkazu JOIN. Fungují úplně stejně, jediný rozdíl je v tom, co se stane, když položka, na kterou se vazba odkazuje, neexistuje.

INNER JOIN

Pokud uvedeme v SQL dotazu pouze JOIN, pokládá ho PostgreSQL databáze za tzv. INNER JOIN. Pokud by v našem případě neexistoval uživatel s ID, které je u článku uvedeno, článek bez uživatele by vůbec nebyl ve výsledcích obsažen. Vazba je nerozdělitelná.

Pojďme si to zkusit, přidejme si článek, který bude odkazovat na ID neexistujícího uživatele:

INSERT INTO clanek (autor_id, popis, url, klicova_slova, titulek, obsah, publikovano) VALUES
(99, 'Článek s neexistujím uživatelem slouží pro vyzkoušení typů JOINů.', 'clanek-bez-autora', 'clanek, join, autor, chybejici', 'Článek bez autora', 'Tento článek je přiřazen neexistujícímu uživateli s ID 99 a slouží k vyzkoušení různých typů JOINů v PostgreSQL databázi.', '2012-10-21');

Vložený článek se odkazuje na uživatele s uzivatel_id 99, který v databázi není. Spusťme si nyní znovu náš SQL dotaz s JOIN. Pro přehlednost je lepší uvést, že chceme INNER JOIN:

SELECT titulek, prezdivka
FROM clanek
INNER JOIN uzivatel ON autor_id = uzivatel_id
ORDER BY prezdivka;

Výsledek:

titulek	prezdivka
Bakterie	David
Pacman	David
Cheese Mouse	Denny
Algoritmus	Míša

Výsledek je stále stejný, článek bez autora mezi výsledky není.

LEFT OUTER JOIN

Vnější JOINy umožňují vybírat i ty výsledky, které se nepodařilo spojit z důvodu chybějících položek. Zkusme si tzv. LEFT JOIN, který výsledek uzná, pokud existuje levá část vazby (zde článek) a pravá (ta připojovaná, zde uživatel) neexistuje. Do hodnot sloupců z připojované části se vloží NULL.

SELECT titulek, prezdivka
FROM clanek
LEFT JOIN uzivatel ON autor_id = uzivatel_id
ORDER BY prezdivka;

Výsledek:

titulek	prezdivka
Bakterie	David
Pacman	David
Cheese Mouse	Denny
Algoritmus	Míša
Článek bez autora

Vidíme, že článek se stejně vybral, i když se nepodařilo vybrat pravou část (tedy tu připojovanou, uživatele). Před spojováním tabulek je dobré se zamyslet, zda nastane případ, kdy se spojení nepodaří a co v tom případě chceme dělat. U článku by se toto v reálu stát asi nemělo.

RIGHT OUTER JOIN

Podobně jako levý vnější JOIN uznal vazbu v případě, že levá část existovala, pravý JOIN to udělá naopak. Pokud bude existovat uživatel (pravá, připojovaná část) a nebude k němu existovat článek (levá část), bude stejně v tabulce zahrnut. Osobně jsem tento JOIN ještě nepoužil. V tabulce jednoho takového uživatele máme, je jím uživatel Ema. Zkusme si tedy RIGHT JOIN:

SELECT titulek, prezdivka
FROM clanek
RIGHT JOIN uzivatel ON autor_id = uzivatel_id
ORDER BY prezdivka;

Výsledek:

titulek	prezdivka
Bakterie	David
Pacman	David
Cheese Mouse	Denny
	Ema
Algoritmus	Míša

Podle očekávání zmizel článek bez autora a objevila se Ema.

Ještě nějaké příkazy JOIN bychom určitě v PostgreSQL nalezli, ale pro naše účely nám toto bohatě stačí.

Wherování

Teoreticky se můžeme JOINům vyhýbat a používat místo nich jednoduše jen klauzuli FROM a WHERE. Ve FROM uvedeme více tabulek oddělených čárkami. Ve WHERE specifikujeme podmínku spojení tabulek. Databáze si v ideálním případě takovýto dotaz nejprve převede na INNER JOIN a poté ho zpracuje.

SELECT titulek, prezdivka
FROM clanek, uzivatel
WHERE autor_id = uzivatel_id
ORDER BY prezdivka;

Výsledek je tedy stejný jako při příkazu INNER JOIN:

titulek	prezdivka
Bakterie	David
Pacman	David
Cheese Mouse	Denny
Algoritmus	Míša

Nevýhoda wherování je, že tak neuděláme všechny JOINy a v určitých případech mohou být dotazy méně optimalizované. Nikdy nevíme, jak dotaz databáze optimalizuje a optimalizace se bude lišit podle typu databáze. Tento způsob berte spíše jako zajímavost a nepoužívejte ho.

Cizí klíče

Při probírání LEFT OUTER JOIN vás možná napadlo, že by určitě nebylo dobré mít články bez autora. V příkladu jsme vložili článek s ID neexistujícího autora.

V jedné z předcházejících lekcí jsme se seznámili s omezeními (constraints), která můžeme aplikovat na sloupce v tabulce (PRIMARY KEY, UNIQUE, NOT NULL). Dalším užitečným omezením je FOREING KEY. Toto omezení říká, že tento sloupec slouží jako vazba (odkaz) na záznam v jiné tabulce. Toto omezení se hodí právě pro použití v tabulce clanek. Tak jako primární klíč (PRIMARY KEY) říká, že hodnota v daném sloupci musí být jedinečná a nesmí být NULL, tak cizí klíč (FOREIGN KEY) říká, že hodnota v tomto sloupci odkazuje na hodnotu ve sloupci jiné tabulky.

Upravme tedy tabulku clanek:

DROP TABLE IF EXISTS clanek;

CREATE TABLE clanek (
    clanek_id integer GENERATED ALWAYS AS IDENTITY,
    autor_id integer NOT NULL,
    popis varchar(155),
    url varchar(155),
    klicova_slova varchar(155),
    titulek varchar(155),
    obsah text,
    publikovano timestamp,
    PRIMARY KEY (clanek_id),
    FOREIGN KEY (autor_id) REFERENCES uzivatel (uzivatel_id)
);

Na sloupec autor_id jsme použili omezení FOREIGN KEY (cizí klíč), které odkazuje do tabulky uzivatel do sloupce uzivatel_id. Navíc autor_id nesmí být NULL.

Nyní vložíme článek s ID existujícího autora:

INSERT INTO clanek (autor_id, popis, url, klicova_slova, titulek, obsah, publikovano) VALUES
(1, 'Článek s existujím uživatelem', 'clanek-s-autorem', 'clanek, autor', 'Článek s autorem', 'Tento článek je bez autora', current_timestamp);

Příkaz proběhl úspěšně.

A nyní se pokusíme vložit článek s ID neexistujícího autora:

INSERT INTO clanek (autor_id, popis, url, klicova_slova, titulek, obsah, publikovano) VALUES
(99, 'Článek bez existujícího uživatele', 'clanek-bez-autora', 'clanek, autor', 'Článek bez autora', 'Tento článek je přiřazen neexistujícímu uživateli s ID 99', current_timestamp);

Příkaz se neprovede a zobrazí se následující chyba:

ERROR:  insert or update on table "clanek" violates foreign key constraint "clanek_autor_id_fkey"
DETAIL:  Key (autor_id)=(99) is not present in table "uzivatel".
SQL state: 23503

Databáze nás upozorňuje na to, že v tabulce uzivatel neexistuje ID s číslem 99. Pokud si nyní zobrazíte všechny uložené články, tento tam nebude. Databáze se postarala o to, aby tento dle definice nevalidní záznam nebyl uložen.

Další věcí, kterou za nás databáze ohlídá, je mazání záznamů v tabulce uzivatel. Zkusme nyní vymazat uživatele s ID 1 (autora článku uloženého v databázi):

DELETE FROM uzivatel WHERE uzivatel_id = 1;

Místo vymazání uživatele jsme dostali chybu:

ERROR:  update or delete on table "uzivatel" violates foreign key constraint "clanek_autor_id_fkey" on table "clanek"
DETAIL:  Key (uzivatel_id)=(1) is still referenced from table "clanek".
SQL state: 23503

Databáze nás upozorňuje na to, že na uživatele s ID 1 je odkazováno z tabulky clanek, a proto jej nemůže vymazat. Tímto databáze zabránila tomu, abychom nechtěně smazali uživatele, který napsal nějaký článek (článek nemůže být bez autora).

Pokud přesto tohoto uživatele budeme chtít smazat, musíme nejdříve smazat jeho články, protože článek bez autora nemůže existovat. S takovýmto omezením jsme tabulku nadefinovali a databáze pak při změnách v tabulce hlídá, zda tato omezení nebyla porušena.

V další lekci, PostgreSQL - Modely databáze a vazby, budeme vytvářet dotazy přes více tabulek a přidáme si do naší databáze další část redakčního systému.