<-- PostgreSQL  -->

• K čemu je to dobré
• PRIMARY KEY
• REFERENCES
• Příklad

Nyní je na čase oprášit si některé pojmy, jako jsou relace, primární klíč a cizí klíč. Budeme je teď hodně potřebovat. Spojování tabulek pomocí klíčů je jedna z velkých předností relačních databází (databází obecně). V této kapitole si na příkladech vysvětlíme, jak tabulky spojovat. Většina práce se spojováním tabulek je na příkazu SELECT. Tento příkaz budeme probírat podrobně v dalších kapitolách, zde si jen ukážeme proč a jak tabulky spojovat.

Příkaz SELECT jsme zatím používali k vrácení tabulky, nebo její části (vybrali jsme sloupce nebo pomocí podmínky WHERE jen některé řádky). Nyní budeme chtít, aby nám příkaz SELECT vrátil tabulku vzniklou spojením některých částí několika jiných tabulek. Než se však pustíme do spojování tabulek, musíme si vysvětlit proč a jak je vytvořit.

K čemu je to dobré

Zatím jsme si ukazovali jen jednoduché databáze, kde jsme vystačili s jednou tabulkou a několika sloupci. U velkých databází by jsme s takovým přístupem asi příliš neobstáli. Představte si, že jste ve velkém podniku, musíte si vést záznamy o svých zaměstnancích, jak dlouho u Vás pracují, kolik berou, kdy dostali prémie, co je náplní jejich práce, v jakém jsou oddělení, atd. Musíte si také vést záznamy o pracovních odděleních, kolik tam pracuje lidí, jací lidé, záznamy o zakázkách, které Vaše firma dostala, kdo a kdy, na zakázce pracoval, kdo jí zadal atd. Nacpat to všechno do jedné tabulky je nad lidské síly.

Řešením je vytvořit několik tabulek. První tabulka bude obsahovat informace o zaměstnancích (tabulka zamestnanci: Jméno, věk, datum přijetí, v jakém oddělení pracuje a na jaké platové úrovni atp.), v další tabulce budou informace o odděleních (tabulka oddeleni: název oddělení, platové úrovně atp.), v další tabulce budou informace o zakázkách (tabulka zakazky: zákazník, cena zakázky, datum objednávky, datum vyhotovení zakázky, které oddělení na zakázce pracovalo) atp.

PRIMARY KEY

Jak ale vložit informace do tabulky zamestnanci o tom, ve kterém oddělení zaměstnanec pracuje? Mohli by jsme tam vkládat název oddělení. To má ovšem své nevýhody. Může se stát, že bude v podniku více oddělení se stejným názvem. Potom se také může název oddělení změnit a pak je třeba upravit nejen tabulku oddeleni, ale také tabulku zamestnanci a vůbec všechny tabulky, kde se na dané oddělení odkazujeme.

Tyto problémy se jednoduše vyřeší pomocí primárního klíče - evidenčních čísel. Každé oddělení bude mít své jedinečné číslo a tím se všechny problémy odstraní. Toto číslo je tedy primárním klíčem tabulky oddeleni.

Sloupcové omezení PRIMARY KEY nám zajišťuje dvě věci. Jednak bude klíč jedinečný, a také (na rozdíl od UNIQUE) že se na tento sloupec můžeme odkazovat z jiné tabulky (relace !) pomocí REFERENCES.

Poznámka: Při vytvoření primárního klíče se vytvoří index.

REFERENCES

REFERENCES je sloupcové omezení, které říká, že hodnoty ve sloupci budou primárními klíči z jiné tabulky. Jinak řečeno, že jde o sloupec cizích klíčů. Za klíčovým slovem REFERENCES následuje název tabulky, do které se budeme odkazovat (např. do oddeleni) a v závorce za názvem tabulky bude název sloupce s primárními klíči této tabulky:
REFERENCES oddeleni (prim_klic)

Z toho vyplývá, že je nejdříve třeba vytvořit tabulku, do které se odkazujeme a až poté tabulku, do které odkazy ukládáme. Při vytvoření reference se vytvoří index.

V tomto sloupci nemusí být jedinečné hodnoty. Určitě bude více zaměstnanců, kteří pracují v jednom oddělení.

Příklad

Kdybych ukazoval příklady pro jednotlivé odstavce této kapitoly, nebyly by vidět potřebné souvislosti. Všechno si tedy osvětlíme na jednom velkém příkladu. S tímto příkladem budeme pracovat i v dalších lekcích.

Protože je tento příklad dosti rozsáhlý, všechny příkazy jsem pro vás uložil do souboru psql6.sql. Jak jej můžete použít se dočtete v kapitole Začínáme s PostgreSQL.

Příklad obsahuje dvě tabulky. První, oddeleni, obsahuje primární klíč s názvem prim_klic, druhá, zamestnanci, obsahuje odkaz na sloupec primárních klíčů první tabulky s názvem oddeleni_id. Než začneme tabulky vytvářet, musíme se ujistit, že tabulky neexistují. Proto na začátku souboru psql6.sql mažeme tabulky příkazem DROP TABLE a též musíme smazat sekvence, které vzniknou použitím datového typu SERIAL jako primárního klíče. (Indexy primárních klíčů a referencí se smažou při smazání tabulky). Pokud tabulky (sekvence) neexistují, vypíše se ERROR, ale to ničemu nevadí!

DROP TABLE oddeleni;
DROP SEQUENCE oddeleni_prim_klic_seq;
DROP TABLE zamestnanci;

Nyní vytvoříme tabulku s názvem oddeleni, která bude obsahovat jen název oddělení, telefon do daného oddělení a primární klíč typu SERIAL se jménem prim_klic.
Druhá tabulka zamestnanci bude obsahovat jméno a příjmení zaměstnance, jeho plat, taktéž primární klíč (rodné číslo) a číslo oddělení, ve kterém zaměstnanec pracuje oddeleni_id. Pomocí sloupce oddeleni_id se odkazujeme z tabulky zamestnanci do tabulky oddeleni (proto je nutné vytvořit nejdříve tabulku oddeleni). Tabulka zamestnanci využívá rodné číslo jako svůj primární klíč, ale v tomto příkladě to není podstatné.

CREATE TABLE oddeleni (nazev VARCHAR(20), telefon VARCHAR(20),
prim_klic SERIAL NOT NULL PRIMARY KEY);

CREATE TABLE zamestnanci (jmeno VARCHAR(20), prijmeni VARCHAR(20),
plat INTEGER CHECK (plat >= 0), oddeleni_id INTEGER REFERENCES
oddeleni(prim_klic), rodne_cislo CHAR(10) NOT NULL PRIMARY KEY);

Nyní vložíme záznamy do tabulky oddělení. Možná Vás napadlo, že by jste mohli použít jako primární klíč telefonní číslo. Možné to je, ale mohli by nastat problémy v případě přečíslování a navíc je telefonní číslo zbytečně dlouhé (těžko budete mít v podniku milión oddělení). To jsou všechno věci, které musíte při návrhu tabulek zvážit sami.

INSERT INTO oddeleni(nazev,telefon) VALUES ('Sekretariat','2 123 123 12');
INSERT INTO oddeleni(nazev,telefon) VALUES ('Pravni oddeleni','2 123 123 11');
INSERT INTO oddeleni(nazev,telefon) VALUES ('Pravni oddeleni','2 123 123 13');

Teď vložíme do databáze záznamy o zaměstnancích. Čísla primárních klíčů jednotlivých oddělení jsou díky typu SERIAL postupně 1,2,3. Přesvědčit se o tom můžete příkazem SELECT.

Pokud se pokusíte vložit do tabulky záznam s již existujícím primárním klíčem (v našem případě stejným rodným číslem, nepodaří se Vám to. Stejně tak, pokud se pokusíte vložit záznam s cizím klíčem, který neexistuje v tabulce, do které se pomocí něj odkazuje (v našem případě číslo oddělení). Pozor: pokud smažete záznam o oddělení, zůstanou Vám v tabulce zaměstnanců řádky z cizími klíči, které odkazují do zrušeného oddělení! (Logicky, nemůžete mít přece zaměstnance v neexistujícím oddělení, proto před nebo po zrušení oddělení převeďte zaměstnance do jiného oddělení (nebo je vyhoďte :-)).

INSERT INTO zamestnanci VALUES ('Lenka','Pavova',10000,1,'8001010601');
INSERT INTO zamestnanci VALUES ('Jana','Pavova',10000,1,'8001010602');
INSERT INTO zamestnanci VALUES ('Jana','Mala',12000,1,'8001010603');
INSERT INTO zamestnanci VALUES ('Lenka','Pavova',15000,2,'8001010604');
INSERT INTO zamestnanci VALUES ('Tom','Jerry',15000,2,'8001010605');
INSERT INTO zamestnanci VALUES ('Martin','Luter',12000,2,'8001010606');
INSERT INTO zamestnanci VALUES ('Leopold','King',13000,2,'8001010607');
INSERT INTO zamestnanci VALUES ('Tomas','Mann',22000,3,'8001010608');
INSERT INTO zamestnanci VALUES ('Vasek','Trn',16000,3,'8001010609');

Tabulky, které jsme teď vytvořily, budeme používat v dalších lekcích.

<-- PostgreSQL  -->