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.