8. díl - PostgreSQL - Dotazy přes více tabulek - dokončení

PostgreSQL PostgreSQL - Dotazy přes více tabulek - dokončení

V minulém dílu seriálu tutoriálů o PostgreSQL databázi jsme si ukázali dotazy přes více tabulek pomocí JOIN. Dnes v tomto tématu budeme pokračovat. Vysvětlíme si rozdíly mezi INNER 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 JOINem. 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:

Pacman          David
Bakterie        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:

Pacman               David
Bakterie             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:

Pacman          David
Bakterie        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é JOINy 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 INNER JOINu:

Pacman          David
Bakterie        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 JOINu 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.

DROP TABLE IF EXISTS clanek;

CREATE TABLE clanek (
        clanky_id serial,
        autor_id integer NOT NULL,
        popis varchar(155),
        url varchar(155),
        klicova_slova varchar(155),
        titulek varchar(155),
        obsah text,
        publikovano timestamp,
        PRIMARY KEY (clanky_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".

********** Error **********

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

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 uzivate 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".

********** Error **********

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

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.

Příště budeme pokračovat v dotazech přes více tabulek a přidáme si do naší databáze další část redakčního systému.


 

  Aktivity (1)

Článek pro vás napsal vita
Avatar
vita

Jak se ti líbí článek?
Celkem (2 hlasů) :
55555


 



 

 

Komentáře

Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zatím nikdo nevložil komentář - buď první!