8. diel - PostgreSQL - Otázky cez viac tabuliek - dokončenie

V predchádzajúcom cvičení, Riešené úlohy k 1.-5. lekciu PostgreSQL, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

V minulom dieli seriálu tutoriálov o PostgreSQL databáze sme si ukázali otázky cez viac tabuliek pomocou JOIN. Dnes v tejto téme budeme pokračovať. Vysvetlíme si rozdiely medzi INNER a OUTER JOIN a zoznámime sa s cudzím kľúčom (FOREIGN KEY).

INNER JOIN a OUTER JOIN

INNER (vnútorná) a OUTER (vonkajšie) JOIN sú 2 typy príkazu JOIN. Fungujú úplne rovnako, jediný rozdiel je v tom, čo sa stane, keď položka, na ktorú sa väzba odkazuje, neexistuje.

INNER JOIN

Ak uvedieme v SQL dotaze len JOIN, pokladá ho PostgreSQL databázy za tzv. INNER JOIN. Ak by v našom prípade neexistoval užívateľ s id, ktoré je u článku uvedené, článok bez užívateľa by vôbec nebol vo výsledkoch obsiahnutý. Väzba je nerozdeliteľná.

Poďme si to skúsiť, pridajme si článok, ktorý bude odkazovať na id neexistujúceho užívateľa:

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ánok sa odvoláva na užívateľov s uzivatel_id 99, ktorý v databáze nie je. Spustite si teraz znovu náš SQL dotaz s JOIN. Pre prehľadnosť je lepšie uviesť, že chceme INNER JOIN.

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

výsledok:

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

Výsledok je stále rovnaký, článok bez autora medzi výsledkami nie je.

LEFT OUTER JOIN

Vonkajšie join umožňujú vyberať aj tie výsledky, ktoré sa nepodarilo spojiť z dôvodu chýbajúcich položiek. Skúsme si tzv. LEFT JOIN, ktorý výsledok uzná, ak existuje ľavá časť väzby (tu článok) a pravá (tá pripájané, tu užívateľ) neexistuje. Do hodnôt stĺpcov z pripojovanej časti sa vloží NULL.

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

výsledok:

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

Vidíme, že článok sa rovnako vybral, aj keď sa nepodarilo vybrať pravú časť (teda tú pripojenú, používateľa). Pred spájaním tabuliek je dobré sa zamyslieť, či nastane prípad, keď sa spojenie nepodarí a čo v tom prípade chceme robiť. Pri článku by sa toto v reáli stáť asi nemalo.

Right OUTER JOIN

Podobne ako ľavý vonkajší JOIN uznal väzbu v prípade, že ľavá časť existovala, pravý JOIN to urobí naopak. Pokiaľ bude existovať užívateľ (pravá, pripájané časť) a nebude k nemu existovať článok (ľavá časť), bude rovnako v tabuľke zahrnutý. Osobne som tento JOIN ešte nepoužil. V tabuľke jedného takého používateľa máme, je ním používateľ Ema. Skúsme si teda RIGHT JOIN:

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

výsledok:

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

Podľa očakávaní zmizol Článok bez autora a objavila sa Ema.

Ešte nejaké join by sme určite v PostgreSQL našli, ale pre naše účely nám toto bohato stačí.

Wherování

Teoreticky sa môžeme JOINům vyhýbať a používať namiesto nich jednoducho len klauzulu FROM a WHERE. Vo FROM uvedieme viac tabuliek oddelených čiarkami. Vo WHERE špecifikujeme podmienku spojenie tabuliek. Databáza si v ideálnom prípade takýto dotaz najprv prevedie na INNER JOIN a potom ho spracuje.

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

Výsledok je teda rovnaký ako pri INNER JOIN:

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

Nevýhoda wherování je, že tak neurobíme všetky join a v určitých prípadoch môžu byť otázky menej optimalizované. Nikdy nevieme, ako dotaz databázy optimalizuje a optimalizácia sa bude líšiť podľa typu databázy. Tento spôsob berte skôr ako zaujímavosť a nepoužívajte ho.

Cudzie kľúče

Pri preberaní LEFT OUTER JOIN vás možno napadlo, že by určite nebolo dobré mať články bez autora. V príklade sme vložili článok s id neexistujúceho autora.

V jednej z predchádzajúcich lekcií sme sa zoznámili s obmedzeniami (constraints), ktorá môžeme aplikovať na stĺpce v tabuľke (PRIMARY KEY, UNIQUE, NOT NULL). Ďalším užitočným obmedzením je FOREING KEY. Toto obmedzenie hovorí, že tento stĺpec slúži ako väzba (odkaz) na záznam v inej tabuľke. Toto obmedzenie sa hodí práve pre použitie v tabuľke clanek. Tak ako primárny kľúč (PRIMARY KEY) hovorí, že hodnota v danom stĺpci musí byť jedinečná a nesmie byť NULL, tak cudzí kľúč (FOREIGN KEY) hovorí, že hodnota v tomto stĺpci odkazuje na hodnotu v stĺpci inej tabuľky.

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 stĺpec autor_id sme použili obmedzenia FOREIGN KEY (cudzí kľúč), ktoré odkazuje do tabuľky uzivatel do stĺpca uzivatel_id. Naviac autor_id nesmie byť null.

Teraz vložíme článok s id existujúceho 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);

Príkaz prebehol úspešne. A teraz sa pokúsime vložiť článok s id neexistujúceho 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);

Príkaz sa nevykoná a zobrazí sa nasledujúca 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áza nás upozorňuje na to, že v tabuľke uzivatel neexistuje id s číslom 99. Ak si teraz zobrazíte všetky uložené články, tento tam nebude. Databáza sa postarala o to, aby tento podľa definície nevalidný záznam nebol uložený.

Ďalšou vecou, ktorú za nás databázy postráži, je mazanie záznamov v tabuľke uzivatel. Skúsme teraz vymazať užívateľov s id 1 (autora článku uloženého v databáze)

DELETE FROM uzivatel WHERE uzivatel_id = 1;

Namiesto vymazanie užívateľa sme 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áza nás upozorňuje na to, že na používateľa s id 1 je odkazované z tabuľky clanek a preto ho nemôže vymazať. Týmto databázy zabránila tomu, aby sme nechtiac zmazali používateľa, ktorý napísal nejaký článok (článok nemôže byť bez autora).

Ak napriek tomu tohto používateľa budeme chcieť zmazať, musíme najskôr zmazať jeho články, pretože článok bez autora nemôže existovať. S takýmto obmedzením sme tabuľku nadefinovali a databázy potom pri zmenách v tabuľke stráži, či tieto obmedzenia nebola porušená.

Nabudúce budeme pokračovať v dotazoch cez viac tabuliek a pridáme si do našej databázy ďalšiu časť redakčného systému.