ARCHIV

PostgreSQL (14) - omezení dat (Constraints)

Omezení umožňují přenést na úroveň databáze hlídání hodnot vkládaných dat. Proč je výhodné je používat se dozví čtenář v tomto díle seriálu.

3.8.2005 07:00 | MaReK Olšavský | Články autora | přečteno 20216×

S jedním omezením již byl čtenář seznámen. Jedná se o zákaz vložení prázdné hodnoty do sloupce, který se definoval při vytváření tabulky direktivou NOT NULL. Vůči této definici existuje i možnost inverzního "omezení", kdy se položka věty určí direktivou NULL, což ale neznamená že by tento sloupec mohl obsahovat pouze prázdnou hodnotu (NULL), ale sloupec se bude chovat tak, že prázná hodnota pro něj bude výchozí, tj. jedná se v podstatě o definici DEFAULT.

Velmi často je při tvorbě tabulek v databázi vidět duplicitní definice typu 'NOT NULL DEFAULT hodnota', která zároveň říká, že hodnota nesmí být prázdná a předepisuje jednu výchozí. Pokud je jednou zadána defaultní hodnota, je tato použita automaticky, pokud není vložena jiná, čiže je část definice NOT NULL teoreticky zbytečná.

Omezení 'CHECK'

Check znamená kontrolu hodnoty ve sloupci. Využívá se v (obvykle) případě, že je potřeba explicitně předepsat rozsah hodnot, kterých může hodnota ve sloupci nabývat. Vhodný příklad je uveden v původní dokumentaci a tím je zakázání záporné hodnoty u ceny zboží v katalogu, v tomto případě je omezen stejným způsobem i stav skladu:

CREATE TABLE produkty(
  id BIGSERIAL,
  nazev VARCHAR(255),
  popis TEXT,
  sklad_kusu NUMERIC(5,0) CHECK (sklad_kusu > = 0),
  cena NUMERIC(10,2) CHECK (cena >=0)
)

Píše se stejným způsobem, jako výchozí hodnota, nebo omezení NOT NULL (které ostatně lze pomocí CHECK také přepsat). Na pořadí DEFAULT, NOT NULL a CHECK v podstatě nezáleží. Způsobů definice kontroly existuje několik, lze je také zapsat jinam, než přímo za hodnotu, ke které se vztahuje a lze je provázat mezi sebou. Tyto alternativní zápisy budou ukázány na několika příkladech, v nichž bude definována stejná, nebo podobná tabulka, jako v předchozím.

-- Definice s check mimo příslušný typ
CREATE TABLE produkty(
  id BIGSERIAL,
  nazev VARCHAR(255),
  popis TEXT,
  sklad_kusu NUMERIC(5,0),
  cena NUMERIC(10,2),
  CHECK (cena >=0),
  CHECK (sklad_kusu > = 0)
)

-- Definice provázaných kontrol
CREATE TABLE produkty(
  id BIGSERIAL,
  nazev VARCHAR(255),
  popis TEXT,
  sklad_kusu NUMERIC(5,0),
  cena_nakup NUMERIC(10,2),
  cena_prodej NUMERIC(10,2),
  CHECK (cena_nakup >=0),
  CHECK (cena_prodej >=0 AND cena_nakup < cena_prodej),
    CHECK (sklad_kusu > = 0)
)


-- Přepsáni NOT NULL ;-)
CREATE TABLE produkty(
  id BIGSERIAL,
  nazev VARCHAR(255) CHECK (nazev IS NOT NULL),
  popis TEXT,
  sklad_kusu NUMERIC(5,0),
  cena NUMERIC(10,2),
  CHECK (cena >=0),
  CHECK (sklad_kusu > = 0)
)

Unikátní hodnota

V mnoha případech je potřeba omezit duplicitu hodnot v jednotlivých větách, například přihlašovací jména, kódy zboží, ... K vynucení tohoto na straně serveru je možné použít klauzuli UNIQUE. Unikátní může být jak hodnota jednoho sloupečku, tak kombinace několika sloupců. Například v ČR rodné číslo není unikátní identifikátor osoby (existují duplicity), ale v kombinaci s místem narození a nebo rodným příjmením, by již jedinečným identifikátorem býti mohl.

-- Unikátní login
CREATE TABLE users(
  id BIGSERIAL,
  login VARCHAR(255) UNIQUE NOT NULL,
  heslo CHAR(40)
);


-- Totéž jinak
CREATE TABLE users(
  id BIGSERIAL,
  login VARCHAR(255) NOT NULL,
  heslo CHAR(40),
  UNIQUE (login)
);


-- Unikátní 3 sloupečky
CREATE TABLE user_details(
  id BIGSERIAL,
  id_user INTEGER UNIQUE,
  jmeno VARCHAR(50),
  prijmeni VARCHAR(100),
  rodcis NUMERIC(10,0),
  UNIQUE (jmeno, prijmeni, rodcis)
);


--Pokud je potřeba omezení pojmenovat
CREATE TABLE users(
  id BIGSERIAL,
  login VARCHAR(255) CONSTRAINT neprazdny_login UNIQUE,
  heslo CHAR(40)
);

Primární klíče

Primární klíč jako takový je, technicky vzato, kombinace dvou omezení, která byla popsána výše, NOT NULL a UNIQUE. Podobně jako UNIQUE je možné jej definovat hned u definice sloupce, nebo na konci definice tabulky. Tabulka smí mít pouze jediný primární klíč, byť zde je menší ústupek vůči SQL normě, která tvrdí, že tabulka musí mít právě jeden primární klíč. Kombinací NOT NULL a UNIQUE lze mít na tabulce "nekonečné množství", ale i zde platí, že všeho s mírou, protože udělat UNIQUE na všechna políčka by mohlo vést k tomu, že brzo bude neschopná vložení jakýchkoliv dat kvůli konfliktům.

--Vytvoření pro jeden sloupec
CREATE TABLE users(
  id BIGSERIAL,
  login VARCHAR(255) PRIMARY KEY,
  heslo CHAR(40)
);


--Vytvoření pro několik sloupců
CREATE TABLE user_details(
  id BIGSERIAL,
  id_user INTEGER UNIQUE,
  jmeno VARCHAR(50),
  prijmeni VARCHAR(100),
  rodcis NUMERIC(10,0),
  PRIMARY KEY (jmeno, prijmeni, rodcis)
);

Cizí klíče

Cizí klíče jsou omezením dat ve sloupci, které pak musí odpovídat hodnotám ktréhokoliv řádku v jiné tabulce. Jedná se tudíž o vynucení referenční integrity mezi tabulkami. Zde platí, že závislá tabulka může být doplněna až poté, co je patřičný záznam v tabulce, na kterou je odkázáno. Vhodným příkladem využití cizích klíčů je v knihovně vazba výpůjčka<->kniha, kde cizí klíč ve výpůjčce je referencí na identifikátor knihy, PgSQL server tudíž nedovolí vložit záznam o výpůjčce knihy, která není uvedena v databázi. Cizí klíče mohou být vázány jak na jeden, tak na několik sloupečků.

Kromě vynucení referencí na ostatní tabulky lze pomocí cizích klíčů relizovat i akce na odkazované tabulce, ačkoliv na to je vhodnější použít triggery (spouště), které jsou přecijen podstatně flexibilnější.

Klíčové slovo, pomocí nějž se tyto cizí klíče definují se jmenuje REFERENCES, za které se uvede sloupec (sloupce) s názvem tabulky, na nichž je vyžadována závislost, případně akce, které se mají provést po změnách v odkazované tabulce.  Akce se definují pomocí klíčových slov ON DELETE, a ON UPDATE. Pro akce navázané na ON DELETE a ON UPDATE jsou k dispozici metody RESTRICT (pro zakázání změny) a CASCADE pro povolení promítnutí změny.

--Vytvoření základních tabulky
CREATE TABLE produkty(
  id BIGSERIAL PRIMARY KEY,
  jmeno VARCHAR(255),
  popis TEXT,
  cena_nakup NUMERIC(10,2) CHECK (cena_nakup>=0),
  cena_prodej NUMERIC(10,2) CHECK (cena_prodej>=0 AND cena_prodej
   >=cena_nakup),
  sklad_kusu NUMERIC(5,0)
);


CREATE TABLE kategorie(
  id BIGSERIAL PRIMARY KEY,
  jmeno VARCHAR,
  popis TEXT
);


--Vytvoření vazební tabulky s cizími klíči
CREATE TABLE produkt_kategorie(
  id_produkt BIGINT REFERENCES produkty(id) ON DELETE RESTRICT,
  id_kategorie BIGINT REFERENCES kategorie(id) ON DELETE CASCADE
);

V příkladě jsou vytvořeny 3 tabulky, z nichž poslední je jednako propojovací mezi prvními dvěma a zároveň má pomocí klíčů vynucenu referenční integritu, aby nebylo možné vložit identifikátory produktů a kategorií, jež nemají v databázi záznamy. Zároveň jsou na této tabulce definovány dvě akce, že řádek se nemá rušit, když je smazán referenční záznam v tabulce produkty a řádek se má smazat, když je smazán řádek v tabulce kategorie (pochopitelně je v tomto menší chyba, protože budou-li mazány produkty, zůstanou na ně v databázi neplatná propojení).

Cizí klíče lze definovat i na konci vytváření tabulky, pomocí klíčových slov FOREIGN KEY (sloupečky) REFERENCES druhá_tabulka (sloupečky). Definice tabulky detailů produktů by mohla vypadat v tom případě vypadat takto:

CREATE TABLE produkty_detail(
  detail_id BIGSERIAL,
  id BIGINT,
  hmotnost NUMERIC(5,2),
  barva INT REFERENCES kody_barev(kod) ON DELETE CASCADE,
  FOREIGN KEY (id) REFERENCES produkty
);

Název tabulky, vůči které se vytváří reference, nemusí nutně obsahovat i názvy sloupečků, v tom případě se sloupeček, pro který je omezovací podmínka, v podobě cizího/vynuceného klíče a sloupeček v referenční tabulce musí jmenovat stejně. V příkladě uvedeném výše, kdyby se změnil v tabulce kategorie sloupeček id přejmenoval na id_kategorie, tak by druhý řádek definice tabulky produkt_kategorie mohl vypadat takto: id_kategorie BIGINT REFERENCES kategorie ON DELETE CASCADE. Cizí klíče lze definovat pouze proti primárnímu klíči.

Práce s omezeními

Práce s omezeními není nikterak složitá. Data lze do databáze zadávat v podstatě bez omezení a hlídat pouze, zda-li server nevrátí chybové hlášení, případně jaké a na něj teprve reagovat v aplikaci, například zobrazením vstupního formuláře pro opravu dat. Tento přístup však není z nejvhodnějších, mnohem systematičtější je kontroly, je-li to možné, provádět na aplikační vrstvě a z databázové vrstvy, v podstatě pro jistotu, pouze číst chybová hlášení, zda-li přeci jen nebylo některé omezení porušeno.

Závěr

V tomto díle byla shrnutá omezení, která může vývojář zadat pro vstupy dat. V příštím díle bude probrán nástroj, který pomůže udržet integritu dat v databázi a tím jsou transakce.

Verze pro tisk