Tabulky. Primární klíče a generátory
NOTICE: Tento dokument je kapitolou z knihy „The InterBase World“, kterou napsali Alexey Kovyazin a Serg Vostrikov.
InterBase je relační DBMS. To mimo jiné znamená, že všechna data v InterBase jsou uložena jako tabulky. Tabulka, jak je realizována z pohledu SQL, je velmi podobná běžné tabulce, kterou lze nakreslit ručně na list papíru nebo vytvořit v programu, jako je Microsoft Excel. Tabulky v InterBase mají sloupce a řádky, do kterých jsou data umístěna. Tabulka musí mít název, který je jedinečný v rámci jedné databáze. Tabulky jsou hlavním úložištěm informací v databázi, a proto byste měli být při vytváření tabulek velmi opatrní.
Existují pravidla popisující, jak vytvářet tabulky v relační databázi, která odrážejí data reálného světa a zároveň umožňují organizovat efektivní ukládání informací v databázi. Proces aplikace těchto pravidel pro navrhování „správné“ databáze se nazývá normalizace. Slovo „správná“ jsme záměrně uvedli v uvozovkách, protože „normalizovaná databáze“ a „optimalizovaná databáze“ nejsou synonyma. Nemusíte se bezpodmínečně řídit pravidly normalizace - vždy aplikujte korekci pro specifikaci daného problému.
Normalizace tabulek v databázi je podrobně zvážena v knize [14. a proto se nebudeme snažit obsáhnout neobsáhnutelné a vrátíme se k našemu tématu diskuse - k tabulkám InterBase. Podívejme se na syntaxi příkazu DDL (DDL - Data Definition Language, podrobněji viz slovník), který umožňuje vytvářet tabulky:
CREATE TABLE table [EXTERNAL [FILE] “”] ( [, | …]);
Zde table je název vytvářené tabulky, - popis sloupců (někdy budeme říkat - polí) vytvářené tabulky. Možnost table [EXTERNAL [FILE] “”] znamená, že bude vytvořena takzvaná externí tabulka, která není uložena ve společném databázovém souboru, ale v samostatném souboru s názvem . Jak vidíte, vše je jednoduché - definujeme název tabulky a sloupce, které obsahuje. Nyní se podrobně podíváme na to, jak definovat sloupce. Syntaxe vytváření sloupce je popsána následujícím příkazem DDL:
= col { datatype | COMPUTED [BY] (< expr>)} | domain}
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [ ]
[COLLATE collation]
Toto je poměrně velká definice, avšak v definici sloupce je povinná pouze malá část uvedených příkazů. Každý sloupec v tabulce musí mít název, který je jedinečný v rámci tabulky, a také datový typ definovaný příkazem datatype, nebo výraz pro výpočet hodnoty sloupce (pro vypočítané sloupce), nebo doménu (viz níže), definovanou jako domain. Datové typy byly zváženy v kapitole „Datové typy“; proto snadno pochopíte, jak se tvoří SQL-výraz pro vytvoření tabulky.
Připojme se k naší databázi FIRSTBASE.gdb vytvořené dříve v kapitole „Vytvoření databáze“ a zkusme pracovat s tabulkami v praxi. Pokud jde o vytváření, mazání a aktualizaci tabulek, bude vhodný kterýkoli z administrativních nástrojů InterBase - z těch uvedených v aplikaci „Nástroje pro administrátory a vývojáře InterBase“, stejně jako standardní utilita isql.exe ze sady dodávek jakéhokoli klonu InterBase.
Zde je příklad jednoduché tabulky nazvané TABLE_EXAMPLE obsahující 3 pole různých typů:
CREATE TABLE Table_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Tato tabulka ilustruje nejčastěji se vyskytující případ v procesu vývoje databáze. Existují však i jiné metody definování polí. Můžeme například nastavit typ pole pomocí domén. Doména je typ definovaný uživatelem pro pohodlí aplikace určitých kombinací parametrů typu. Například je možné definovat doménu D_ID pro specifikaci polí identifikátorů. Po definování domény ji můžeme použít pro nastavení typu pole:
CREATE DOMAIN D_ID AS INTEGER; CREATE TABLE Тable_example ( ID D_ID, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Pole ID bude mít typ definovaný doménou D_ID. Tímto způsobem, když definujeme typ pole v doméně, požadované kontroly a omezení, můžeme tuto doménu mnohokrát použít pro vytváření polí stejné funkce. Například měnové pole bez zdlouhavého a riskantního kopírování definic proměnných typů, které by mohlo vést k chybám. Třetí způsob, jak nastavit sloupec v tabulce, je definovat jej jako vypočítaný (COMPUTED BY) a specifikovat podmínku, podle které bude jeho hodnota vypočítána. Například můžeme chtít mít v naší tabulce sloupec počítající 10 % z hodnoty pole PRICE_1. V tomto případě by měl být napsán následující příkaz:
CREATE TABLE Тable_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, PRICE_10 COMPUTED BY (PRICE_1.0.1));
Ale nemyslete si, že jakmile vložíme data do pole PRICE_1, v poli PRICE_10 se objeví desetina hodnoty tohoto pole. Ne, proces je zde složitější. Ve skutečnosti získáme požadovanou desetinu pouze při odkazu na pole PRICE_10, například při provádění dotazu SELECT na tuto tabulku. To znamená, že v vypočítaném poli nejsou uložena žádná data a provádí se výpočet výrazu spojeného s polem a výsledek je produkován jako odpověď na dotaz.
Takže jsme zvážili 3 hlavní způsoby specifikace polí v tabulce. Nyní se podrobně podíváme na možnosti, které lze nastavit při vytváření sloupce. Možnost [DEFAULT {literal | NULL | USER}] - umožňuje nastavit výchozí hodnotu sloupce. Je to velmi pohodlné pro automatické vyplňování dat. Existují 3 způsoby, jak nastavit výchozí hodnotu. První je označen jako literal a umožňuje nastavit výchozí hodnoty jako textové konstanty, čísla nebo data. Například můžeme vytvořit následující výrazy pro vytvoření sloupce s textovými výchozími hodnotami: NAME VARCHAR(80) DEFAULT ‘Василий Станиславович’
Tímto způsobem všechna pole vložená do tabulky převezmou výchozí hodnoty, tj. pokud nebyla pro pole NAME definována jiná hodnota, objeví se řetězec ‘Vasily Stanislavovich’. Druhý způsob, jak nastavit výchozí hodnotu, je specifikovat DEFAULT NULL v definici sloupce. A v nově vytvořených záznamech bude hodnota tohoto sloupce NULL, pokud jiná hodnota samozřejmě nebyla explicitně nastavena. Příklad:
PRICE_1 DOUBLE PRECISION DEFAULT NULL
Třetí způsob, jak nastavit výchozí hodnotu, je specifikovat DEFAULT USER v definici sloupce. Tímto způsobem bude toto pole v nově vytvořených záznamech obsahovat jméno aktuálního uživatele, tj. uživatele, který se připojil k InterBase a provedl tento vložení (podrobněji o uživatelích viz kapitola „Bezpečnost v InterBase: uživatelé, jejich funkce a práva“ (část 4)). Pro některá pole je nezbytné, aby pole mělo neprázdnou hodnotu. Například pole, které podle specifikace problému nemůže být prázdné. Aby bylo možné nastavit omezení na úrovni databáze, že pole by mělo mít definovanou hodnotu, je nutné provést následující doplnění k popisu sloupce:
NAME VARCHAR(80) NOT NULL
Tímto způsobem bude existovat pole, ve kterém nelze ukládat hodnoty NULL. Obvykle je omezení NOT NULL kombinováno s možností DEFAULT, která tomuto poli jednoznačně přiřadí správnou hodnotu. Ale často omezení NOT NULL nestačí. Například v případě ukládání cen v databázi je zcela jasné, že nemohou nabývat záporných hodnot (i když by bylo skvělé, kdyby nám při nákupu zboží připláceli). Aby server zkontroloval hodnoty cen vložených do databáze na podmínku pozitivity, je nutné definovat sloupec následujícím způsobem:
PRICE_1 DOUBLE PRECISION CHECK (PRICE_1>0)
Hodnoty vložené do sloupce PRICE_1 budou kontrolovány na podmínku pozitivity. Je třeba poznamenat, že různé konzistentní možnosti lze kombinovat, a například můžeme nastavit neprázdnou hodnotu a kontrolu pozitivity:
PRICE_1 DOUBLE PRECISION NOT NULL CHECK (PRICE_1>0)
Při vytváření sloupců nelze některé možnosti kombinovat, například nelze nastavit NULL jako výchozí hodnotu a současně omezení neprázdné hodnoty. Je třeba poznamenat, že kontroly mohou vykonávat řadu užitečných funkcí pro správu dat v databázi. Jejich použití podrobně zvážíme v kapitole „Omezení databáze“.
Takže jsme zvážili způsoby vytváření tabulek a polí s různými možnostmi. Existují však případy, kdy musíme změnit již existující tabulku. Samozřejmě můžeme tabulku zcela znovu vytvořit. Nejprve bychom měli provést příkaz pro smazání tabulky a poté ji znovu vytvořit. Například:
DROP TABLE Table_example; CREATE TABLE Table_example(ID NUMERIC(15,2);
Ale takový způsob změny tabulek má významné nevýhody. Při mazání tabulky pomocí příkazu DROP jsou všechna data, která tabulka obsahuje, smazána, a aby nedošlo k jejich ztrátě, je nutné je zkopírovat do dočasných tabulek. To je poměrně obtížné. Proto existuje příkaz ALTER TABLE pro snadnou změnu struktury tabulek, který umožňuje přidávat nová pole, mazat existující a také přidávat / odebírat omezení referenční integrity.
Například chceme do tabulky přidat další sloupec určený pro ukládání údajů o patronymu osoby:
ALTER TABLE Table_example ADD Patronimic VARCHAR(80);
Po provedení tohoto příkazu bude mít naše tabulka Table_example nový sloupec s názvem Patronimic a typem VARCHAR (80). Pokud chceme z tabulky odstranit sloupec s názvem NAME, měli bychom provést následující:
ALTER TABLE Table_example DROP Name;
Úplnou syntaxi příkazu ALTER TABLE můžete vidět v [1.. Je to velmi užitečný příkaz a budeme ho často používat.
A co dělat, zeptáte se, pokud je nutné upravit sloupec? Například jsme se rozhodli, že pro ukládání jmen je lepší použít pole HUMAN_NAME než NAME. V tomto případě můžeme použít ALTER TABLE
ALTER TABLE Table_example ALTER COLUMN NAME TO HUMAN_NAME;
Pokud jsme se rozhodli změnit typ pole, například zvýšit počet znaků uložených v poli, budeme muset změnit doménu tohoto pole pomocí příkazu ALTER DOMAIN (viz kapitola „Datové typy“ výše).
Takže jsme zvážili vytváření a úpravy tabulek v InterBase. Nyní je čas ponořit se trochu hlouběji do teorie databází. InterBase, jak již bylo řečeno, je relační databáze. To mimo jiné znamená, že každý záznam v tabulce by měl mít charakteristiku, podle které lze jeden záznam odlišit od druhého. Tomuto účelu slouží speciální mechanismus jedinečných klíčů.
Primární klíče v tabulkách
Samozřejmě můžeme vytvořit tabulku, která neobsahuje žádné klíče. Není nám to zakázáno. Ale, jak bylo řečeno dříve, vytvoření efektivní databáze není možné bez dodržování pravidel normalizace. Přítomnost klíčů je nejdůležitějším prvkem normalizace. Proto, i když se nezaměřujeme na teorii a normalizaci databází, měli bychom zavést definici klíčů a přezkoumat jejich funkci v InterBase. Půjdeme krok za krokem a začneme s nejběžnějším typem klíčů - primárním klíčem.
Takže, co je primární klíč? Je to jedno nebo více polí v tabulce, která jednoznačně identifikují záznamy v rámci této tabulky. Zní to složitě, ale ve skutečnosti je vše velmi jednoduché. Představte si běžnou tabulku, například účetní list. Jaký je první sloupec? Přesně tak, pořadové číslo - 1, 2, 3 … Toto číslo označuje jedinečný řádek v rámci tabulky, a stačí znát toto číslo, abyste našli řetězec v této tabulce. V tomto příkladu to bude primární klíč. Drtivá většina tabulek v relační databázi nutně má primární klíč (PK - zkratka z Primary key). Běžným doporučením při vytváření tabulek je vytvořit primární klíč. Primární klíč lze vytvořit při vytváření tabulky nebo později. Předpokládejme, že v okamžiku vytváření tabulky jsme se rozhodli, že pole ID bude naším primárním klíčem. Pak můžeme přidat primární klíč následujícím způsobem:
CREATE TABLE Table_example ( ID INTEGER NOT NULL, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, CONSTRAINT pkTable PRIMARY KEY (ID));
Co je třeba udělat pro vytvoření primárního klíče pro tabulku table_example? Podívejme se, co se změnilo v definici tabulky? Nejprve sloupec ID získal další definici NOT NULL. To je důležité, protože primární klíč by měl být jedinečný a nesmí obsahovat nedefinované hodnoty. A NULL, jak víte, je nedefinovaná hodnota. Proto všechny pole zahrnutá do primárního klíče by měla mít omezení NOT NULL. Pro dokončení vytvoření primárního klíče je třeba na konci tabulky napsat: CONSTRAINT ()
Úplnou syntaxi omezení najdete v kapitole “Databázová omezení” þ. 1, a pro náš příklad primárního klíče to bude vypadat takto:
CONSTRAINT pkTable PRIMARY KEY (ID)
Zde pkTable je název primárního klíče a ID jsou sloupce, které obsahuje. Tento způsob definování primárních klíčů pro tabulky je vhodný při hromadném vytváření tabulek (například při konstrukci prototypu databáze na základě skriptů získaných z různých CASE nástrojů). Ale co dělat, pokud potřebujeme přidat / odstranit primární klíč k tabulce, která již existuje a je naplněna daty? Pro tento účel by mělo být použito další rozšíření příkazu - ALTER TABLE. Příklad přidání primárního klíče k naší tabulce:
ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT FF PRIMARY KEY (ID);
Tím bude mít tabulka Table_example přesně stejný primární klíč jako v předchozím příkladu, kdy byl vytvořen společně s tabulkou. Pro odstranění primárního klíče by měl být zadán následující příkaz:
ALTER TABLE Table_example DROP CONSTRAINT pkTable;
Tím bude klíč s názvem pkTable odstraněn z databáze.
Generátory - nejlepší přátelé primárních klíčů
Musíme říci několik slov o implementaci primárního klíče. Protože je určen k podpoře jedinečnosti, žádné dva záznamy v jedné tabulce nemohou mít stejné hodnoty tohoto klíče. To znamená, že pro splnění podmínky, při vkládání nového záznamu do tabulky, musí InterBase zkontrolovat všechny záznamy v tabulce a zjistit, zda tabulka obsahuje takové hodnoty nebo ne. Pro rychlé vyhledávání má InterBase mechanismus indexů - speciální objekty InterBase, které umožňují rychle najít záznam v tabulce. Proto při vytváření a odstraňování primárního klíče je vytvořen nebo odstraněn index pro to pole (nebo pole), které je zahrnuto do primárního klíče.
Jak bylo řečeno dříve, primární klíč může obsahovat několik polí. Můžeme si tedy všimnout jedinečnosti kombinace hodnot těchto polí. Například, pokud definujeme klíč pro pole ID a NAME, server bude kontrolovat, že v tabulce neexistují žádné identické kombinace těchto polí. To znamená, že kombinace polí ID a 1 a “Ivanov”, 2 a “Ivanov” budou správné, protože se liší v hodnotách pole ID.
Primární klíč tedy může zahrnovat několik polí libovolných typů. V praxi je však nejběžnějším typem klíče čítač - celočíselné pole, které obsahuje rostoucí hodnoty. Proč tomu tak je? Je to odraz starého sporu mezi přirozenými a náhradními klíči. Koncept přirozených klíčů říká, že jako klíč bychom měli použít hodnoty skutečně existující v datové doméně, kterou databáze odráží. Například, pokud vyvíjíme systém registrace osob pro pasovou kancelář, podle tohoto konceptu by měla být jako primární klíč použita kombinace čísla a série pasu. Opravdu, každá osoba musí mít jedinečnou kombinaci čísla a série pasu. Ale co dělat s tím, že osoba může během života změnit pas (v souvislosti s dosažením určitého věku, sňatkem atd.)? V tomto případě budeme muset změnit číslo a sérii pasu přiřazené konkrétní osobě, tj. vlastně změnit náš primární klíč. To je nežádoucí z hlediska vývoje databázových aplikací: s ohledem na rozvětvený systém vztahů mezi tabulkami (následující kapitola je tomu věnována) bude muset vývojář vynaložit velké úsilí na kontrolu této situace.
Proto se ve většině případů používá náhradní klíč. Náhradní znamená umělý, tj. neexistující v datové doméně, kterou naše databáze popisuje, a uměle vytvořený - pro pohodlí vývoje databázových aplikací. Jak bylo řečeno, obvykle je čítač primárním klíčem. Některé DBMS, jako Paradox a MS SQL, mají speciální typ - čítač (auto increment). Při přidávání nového záznamu do tabulky se hodnota pole s tímto typem automaticky zvyšuje o hodnotu přírůstku - obvykle o jednotky. V InterBase neexistuje pole typu čítač, ale takové chování lze realizovat. Pro vytvoření pole, které by bylo automaticky vyplněno při přidávání záznamu do tabulky, se používá kolekce zdrojů: prvním z nich je generátor.
Co je generátor? Jednoduše řečeno, generátor je pojmenovaný čítač. V rámci databáze můžeme vytvořit čítač, dát mu jedinečný název v rámci této databáze a řídit hodnoty tohoto čítače. To bude generátor. Zde je příklad DDL příkazů, které vám to vysvětlí:
CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;
V prvním řádku tohoto příkladu je vytvořen generátor s názvem g1 a ve druhém řádku je tomuto generátoru přiřazena hodnota 2445. Nyní je otázka, jak použít získaný generátor. V InterBase existuje vestavěná funkce GEN_ID pro získávání a změnu hodnot generátorů. Tato funkce přijímá jako parametry název generátoru a hodnotu přírůstku, který má být aplikován na daný generátor, a vrací celočíselnou hodnotu odpovídající hodnotě generátoru získané jako výsledek přidání přírůstku k němu. Zde je příklad volání funkce GEN_ID v triggeru nebo uložené proceduře:
Current_value = GEN_ID (g1, 1)
Pokud chceme získat hodnotu generátoru, můžeme použít následující dotaz:
SELECT GEN_ID(g1, 1)FROM RDB$ DATABASE
Protože tabulka RDB $ Database vždy obsahuje pouze jeden záznam, získáme hodnotu generátoru g1 jako výsledek daného dotazu.
Zde current_value je proměnná (v následujících kapitolách najdete informace, jak používat proměnné v InterBase), g1 - generátor, 1 - přírůstek. V tomto příkladu se hodnota generátoru g1 dostane do proměnné current_value po přidání přírůstku 1 k němu, tj. následující hodnota generátoru. Všimněte si, že přírůstek nemusí být roven 1! Navíc může být i záporný: Current_value = GEN_ID (g1, -23)
Jako výsledek provedení této funkce bude aktuální hodnota generátoru g1 mínus 23. Jak vidíte, rozsah možných aplikací generátorů je poměrně široký - lze je použít nejen pro získávání hodnot primárních klíčů, ale také pro sledování globálních změn v databázi.
Lidé obeznámení s databázemi se mohou ptát: “Co se stane, když současně několik klientů bude zkoušet vložit data do stejné tabulky a současně “táhnout” generátory? Dostanou stejné nebo různé hodnoty generátoru?” Jednoznačně dostanou RŮZNÉ hodnoty generátoru. Bez ohledu na to, jak “současný” byl pokus o získání hodnoty generátoru, každý, kdo o to požádal, by dostal jedinečnou hodnotu. To je zaručeno “konstrukcí” generátorů: pracují na nejnižší úrovni serveru a žádné procesy zápisu a vkládání je neovlivňují - často se říká, že generátory pracují “mimo kontext transakcí”. Pokud se chcete dozvědět o transakcích, přečtěte si kapitolu “Transakce. Parametry transakcí” (část 1); jak jsou generátory uspořádány - “Struktura databáze InterBase” (část 4). No, jménem generátorů máme spolehlivý mechanismus pro vytváření jedinečných primárních klíčů. Můžeme však tento mechanismus použít? Jak vložit hodnotu získanou z generátoru do pole primárního klíče?
Pro tento účel existují dva způsoby - vložit primární klíč jménem klienta a jménem serveru. Pro zvládnutí prvního způsobu bychom se měli obrátit na kapitolu “Použití hlavních komponent FIBPlus” a pro pochopení druhého - na kapitolu “Triggery” (část 1). Zde stručně probereme hlavní bod obou způsobů.
V případě vytváření primárního klíče jménem klienta se stane následující. Když je generován záznam, který bude vložen do databáze, provede se volání funkce GEN_ID (, 1) a získaná hodnota se dosadí za tento záznam. Poté proběhne vložení do tabulky a máme zaručeno, že obdržíme jedinečný primární klíč.
Druhý způsob - vytváření primárního klíče jménem serveru - obecně eliminuje jakoukoli starost ze strany klienta o to, jaká hodnota primárního klíče bude. V tomto případě při vkládání záznamu pracuje trigger - speciální databázový objekt, který může provádět libovolné operace při vkládání / odstraňování / aktualizaci záznamů v tabulkách. A v tomto triggeru jsou provedeny následující operace: volání funkce GEN_ID, získání požadované hodnoty generátoru a její vložení do tabulky. Výhodou druhého způsobu je, že při vývoji klientské aplikace není třeba se vůbec starat o vytvoření primárního klíče, jediné, co musíte udělat, je napsat potřebný trigger jednou. Nevýhodou je, že nemůžeme získat hodnotu generovaného klíče v aplikaci hned po vložení! Pokud použijeme první způsob, můžeme získat hodnotu primárního klíče, i když se musíme starat o jeho vytvoření při každém vložení. Je těžké říci s jistotou, který způsob je lepší, vše závisí na konkrétním problému. Dále v této knize zvážíme možné varianty řešení otázek týkajících se práce s primárním klíčem.
Závěr
Takže v této kapitole jsme prozkoumali, jak vytvářet a aktualizovat tabulky v InterBase, stejně jako zacházet s primárními klíči. Tím jsme zvážili hlavní objekty v InterBase, které lze podmíněně nazvat statickými, protože pouze ukládají informace a neprovádějí jejich konverzi. Dále budeme hovořit o způsobech řízení informací a konverze informací v rámci databáze.