Lekce 11 - PostgreSQL - Modely databáze a vazby

V minulé lekci, PostgreSQL - Dotazy přes více tabulek - Dokončení, jsme se podívali na INNER a OUTER JOIN a seznámili jsme se s omezením FOREING KEY (cizí klíč).

V dnešním PostgreSQL tutoriálu se zmíníme o různých modelech databáze, jednotlivých vazbách mezi tabulkami a budeme pokračovat v jednoduchém redakčním systému.

V lekci Dotazy přes více tabulek (JOIN) jste mohli vidět konceptuální model vytvářené databáze. Konceptuální modelování představuje tu nejvyšší vrstvu abstrakce při modelování databáze. Z konceptuálního modelu se dozvíte, jaké tabulky mají v databázi existovat a jaké jsou mezi nimi vazby.

O něco podrobnější úroveň modelování představuje logický model. Z logického modelu se dozvíte navíc, jaké sloupce daná tabulka obsahuje, zda jsou data v těchto sloupcích povinná či nikoliv a třeba i to, který sloupec obsahuje primární klíče.

Nejpodrobnější informace získáte z fyzického modelu. Tento model je již závislý na vybrané databázi, protože jednotlivým sloupcům přiřazuje datové typy a jejich délku či přesnost.

Obrázek ukazuje, jak by ve fyzickém modelu mohla vypadat část naší databáze. Tento fyzický model je určen pro Oracle databázi (používá datové typy specifické pro Oracle jako např. VARCHAR2).

Souhrn

• Fyzický model je nejpodrobnější, avšak je platný pouze pro určitou databázi. Poskytuje velké množství informací, avšak u většího modelu se může stát nepřehledným.
• Logický model poskytuje informace nejen o tabulkách a jejich vazbách, ale též o sloupcích v těchto tabulkách a představuje kompromis mezi fyzickým a konceptuálním modelem.
• Konceptuální model dává obecný přehled o tom, jak daná databáze vypadá - jaké obsahuje tabulky a jaké jsou mezi nimi vazby, avšak není příliš podrobný.

Každý z těchto modelů má své opodstatnění a každý z nich se více hodí pro určité situace a pro jiné zase méně.

Logický model

V této části se blíže seznámíme s logickým modelem tak, abychom jej dokázali přečíst a použít pro tvorbu našeho jednoduchého redakčního systému.

Modifikátory atributů

• # označuje sloupec obsahující primární klíče
• * označuje povinný sloupec, hodnota nesmí být NULL
• o označuje nepovinný sloupec, hodnota v tomto sloupci může být NULL

Vazby

Přerušovaná čára znamená nepovinnou vazbu a plná čára vazbu povinnou.

Vazba 1:1

Entita_1 musí mít vazbu na jednu entitu_2 a entita_2 může mít vazbu na jednu entitu_1.

Vazba 1:N

Entita_3 může mít vazbu na jednu nebo více entit_4 a entita_4 musí mít vazbu na jednu entitu_3.

Vazba M:N

Entita_5 může mít vazbu na jednu nebo více entit_6 a entita_6 může mít vazbu na jednu nebo více entit_5.

Úprava tabulek databáze

Nyní podle logického modelu uvedeného výše upravíme tabulky naší databáze.

Tabulka uzivatel

Pro tabulku uzivatel máme tento logický model:

Model nám říká, že sloupec uzivatel_id obsahuje primární klíč (v modelu označen #) a je tím pádem i povinný (NOT NULL, v modelu označen *). Dále z něj lze vyčíst, že sloupec prezdivka může být nepovinný na rozdíl od sloupců email a heslo. Pokud budeme mít uživatele, určitě budeme požadovat minimálně jejich email a samozřejmě heslo. Proto tyto sloupce budou mít omezení (constraint) NOT NULL a databáze nám ohlídá, že každý záznam v této tabulce bude obsahovat hodnotu v tomto sloupci.

Tabulku upravíme následujícími příkazy:

DROP TABLE IF EXISTS uzivatel CASCADE;

CREATE TABLE uzivatel
(
  uzivatel_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
  prezdivka character varying(155),
  email character varying(155) NOT NULL,
  heslo character varying(255) NOT NULL,

  PRIMARY KEY (uzivatel_id)
);

Pokud je sloupec označen jako PRIMARY KEY, je automaticky též NOT NULL. Nic se ale nestane, když to v SQL příkazu při vytváření tabulky znovu uvedeme.

Tabulka clanek

Pro tabulku clanek máme tento logický model:

Sloupec clanek_id obsahuje primární klíče (v modelu označen #) a je zároveň povinný (v modelu označen *). U článku budeme požadovat minimálně titulek a obsah. V modelu jsou tedy označeny znakem * a my jim přidáme omezení NOT NULL. Dalším povinným atributem bude uzivatel_id, což je odkaz (FOREIGN KEY) na záznam v tabulce uzivatel.

Zbylé sloupce budou moci obsahovat hodnotu NULL.

Mezi tabulkou clanek a uzivatel existuje tato vazba:

Říká nám:

Uživatel může psát (být autorem) jeden nebo více článků a článek musí být napsán jedním uživatelem.

K úpravě tabulky tedy použijeme následující příkazy:

DROP TABLE IF EXISTS clanek;

CREATE TABLE clanek
(
  clanek_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
  uzivatel_id integer NOT NULL,
  popis character varying(155),
  url character varying(155),
  klicova_slova character varying(155),
  titulek character varying(155) NOT NULL,
  obsah text NOT NULL,
  publikovano timestamp,

  PRIMARY KEY (clanek_id),
  FOREIGN KEY (uzivatel_id) REFERENCES uzivatel(uzivatel_id)
);

Omezení NOT NULL na sloupci uzivatel_id zajistí, že v tomto sloupci musí být pro každý záznam hodnota. Omezení FOREIGN KEY zas kontroluje, že hodnota ve sloupci uzivatel_id musí existovat ve sloupci uzivatel_id tabulky uzivatel.

Nyní tedy máme dvě tabulky, kdy tabulka clanek má vazbu na tabulku uzivatel.

Klíčové slovo CASCADE

Při vytváření tabulky uzivatel jste si možná všimli tohoto příkazu:

DROP TABLE IF EXISTS uzivatel CASCADE;

Tento příkaz způsobí, že se smaže (DROP TABLE) tabulka uzivatel, ale pouze v případě, že taková tabulka existuje (IF EXISTS). Zároveň, pokud na tuto tabulku odkazují další databázové objekty (v našem případě FOREIGN KEY v tabulce clanek), budou tyto objekty (závislosti) také smazány (CASCADE).

Pokud tedy máte vytvořeny obě tabulky, zkuste provést následující příkaz:

DROP TABLE IF EXISTS uzivatel;

Příkaz skončí chybou:

ERROR:  cannot drop table uzivatel because other objects depend on it
DETAIL:  constraint clanek_uzivatel_id_fkey on table clanek depends on table uzivatel
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
SQL state: 2BP01

Popis chyby nám říká, že není možné vymazat/zahodit (DROP) tabulku uzivatel, protože v databázi existují objekty, které na ní závisí. Je přímo uvedeno, že tabulka clanek závisí na tabulce uzivatel. V části hint (rada) je doporučeno použít DROP s CASCADE:

DROP TABLE IF EXISTS uzivatel CASCADE;

Nyní proběhlo vše v pořádku, bez chyb. Tabulka uzivatel byla smazána, avšak tabulka clanek nikoliv. Možná se ptáte, jak je to možné. Vždyť tabulka článek měla vazbu/závislost (FOREIGN KEY) na tabulku uzivatel, která by toto neměla dovolit. Pokud se podíváte na tabulku clanek pozorně, zjistíte, že tabulka sice nebyla smazána, ale bylo smazáno omezení, které zajišťovalo vazbu na tabulku uzivatel. Jednoduše řečeno byl smazán FOREIGN KEY.

Před:

CREATE TABLE IF NOT EXISTS public.clanek
(
    clanek_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    uzivatel_id integer NOT NULL,
    popis character varying(155),
    url character varying(155),
    klicova_slova character varying(155),
    titulek character varying(155),
    obsah text,
    publikovano timestamp,
    CONSTRAINT clanek_pkey PRIMARY KEY (clanek_id),
    CONSTRAINT clanek_uzivatel_id_fkey FOREIGN KEY (uzivatel_id)
        REFERENCES public.uzivatel (uzivatel_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Po:

CREATE TABLE IF NOT EXISTS public.clanek
(
    clanek_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    uzivatel_id integer NOT NULL,
    popis character varying(155),
    url character varying(155),
    klicova_slova character varying(155),
    titulek character varying(155),
    obsah text,
    publikovano timestamp,
    CONSTRAINT clanek_pkey PRIMARY KEY (clanek_id)
)

Bez použití CASCADE

Pokud nechceme používat CASCADE při mazání tabulek, musíme nejdříve smazat (dropnout) tabulku clanek a poté teprve tabulku uzivatel (přesně v tomto pořadí):

DROP TABLE IF EXISTS clanek;
DROP TABLE IF EXISTS uzivatel;

Výše uvedené příkazy dropnuly obě tabulky. Vytvořte si tedy obě tabulky znovu. Tabulky si naplňte daty ze souboru uzivatel_clanek_data.sql, který naleznete v příloze pod článkem.

V příští lekci, PostgreSQL - Další dotazy a vazba M:N, si vytvoříme v naší databázi další tabulky a blíže se seznámíme s vazbou M:N.

 

Měl jsi s čímkoli problém? Stáhni si vzorovou aplikaci níže a porovnej ji se svým projektem, chybu tak snadno najdeš.