LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> 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 17039×

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

pridej.cz

 

DISKUZE

Omezení jsou důležité 19.5.2006 23:24 Lukáš Zapletal




Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

12.11.2018 21:28 /Redakce Linuxsoft.cz
22. listopadu 2018 se koná v Praze na Karlově náměstí již pátý ročník konference s tématem Datová centra pro business, která nabídne odpovědi na aktuální a často řešené otázky: Jaké jsou aktuální trendy v oblasti datových center a jak je optimálně využít pro vlastní prospěch? Jak si zajistit odpovídající služby datových center? Podle jakých kritérií vybírat dodavatele služeb? Jak volit vhodné součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně datové centrum spravovat? Jak co nejlépe eliminovat možná rizika? apod. Příznivci LinuxSoftu mohou při registraci uplatnit kód LIN350, který jim přinese zvýhodněné vstupné s 50% slevou.
Přidat komentář

6.11.2018 2:04 /František Kučera
Říjnový pražský sraz spolku OpenAlt se koná v listopadu – již tento čtvrtek – 8. 11. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma umění a technologie, IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář

4.10.2018 21:30 /Ondřej Čečák
LinuxDays 2018 již tento víkend, registrace je otevřená.
Přidat komentář

18.9.2018 23:30 /František Kučera
Zářijový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 20. 9. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář

9.9.2018 14:15 /Redakce Linuxsoft.cz
20.9.2018 proběhne v pražském Kongresovém centru Vavruška konference Mobilní řešení pro business. Návštěvníci si vyslechnou mimo jiné přednášky na témata: Nejdůležitější aktuální trendy v oblasti mobilních technologií, správa a zabezpečení mobilních zařízení ve firmách, jak mobilně přistupovat k informačnímu systému firmy, kdy se vyplatí používat odolná mobilní zařízení nebo jak zabezpečit mobilní komunikaci.
Přidat komentář

12.8.2018 16:58 /František Kučera
Srpnový pražský sraz spolku OpenAlt se koná ve čtvrtek – 16. 8. 2018 od 19:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát jsou tématem srazu databáze prezentaci svého projektu si pro nás připravil Standa Dzik. Dále bude prostor, abychom probrali nápady na využití IoT a sítě The Things Network, případně další témata.
Přidat komentář

16.7.2018 1:05 /František Kučera
Červencový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 7. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát bude přednáška na téma: automatizační nástroj Ansible, kterou si připravil Martin Vicián.
Přidat komentář

18.6.2018 0:43 /František Kučera
Červnový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 21. 6. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát na téma: F-Droid, aneb svobodný software do vašeho mobilu. Kromě toho budou k vidění i vývojové desky HiFive1 se svobodným/otevřeným čipem RISC-V.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

5.10.2018 17:12 / Jakub Kuljovsky
Re: Jaký kurz a software by jste doporučili pro začínajcího kodéra?

20.9.2018 10:04 / Jan Ober
Jaký kurz a software by jste doporučili pro začínajcího kodéra?

20.9.2018 10:00 / Jan Ober
Re: Gimp

20.2.2018 18:48 / Ivan Majer
portal

20.2.2018 15:57 / Jan Havel
Jak využíváte služby cloudu v podnikání?

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2018) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze