Omezení databáze ve Firebirdu a InterBase
_NOTICE: Tento dokument je kapitolou z knihy „The InterBase World“, kterou napsali Alexey Kovyazin a Serg Vostrikov.
Tato kapitola je věnována omezením databází InterBase a Firebird. Databázová omezení jsou pravidla, která definují vzájemné vztahy mezi tabulkami a mohou kontrolovat a upravovat data v databázi. Tato pravidla jsou realizována jako speciální databázové objekty. Hlavní výhoda používání omezení spočívá ve schopnosti implementovat kontrolu dat a část business logiky aplikace na úrovni databáze, tj. centralizovat a zjednodušit ji, a tím usnadnit a zefektivnit vývoj databázových aplikací.
Začínající vývojáři často zanedbávají používání databázových omezení, protože se domnívají, že brání tvůrčí práci. Ve skutečnosti je však takový názor založen na nedostatečné znalosti teorie a praxe návrhu databází.
Zároveň ti nejzkušenější návrháři se odvažují odmítnout používání některých typů omezení, díky čemuž jejich aplikace získávají na rychlosti. Zkušenosti expertních návrhářů jim umožňují velmi dobře rozumět práci serveru a přesně předvídat jeho chování ve složitých případech, proto je pro začínající programátory InterBase lepší neuchylovat se k podobným postupům svých zkušených kolegů.
V rámci této knihy se nezabýváme návrhem databází, proto pro více informací k této problematice viz seznam literatury na konci knihy. Zde se pouze podíváme na všechny typy omezení v databázi InterBase a zvážíme příklady jejich použití.
Typy omezení v databázi
V databázi InterBase existují následující typy omezení:
- PRIMARY KEY;
- UNIQUE KEY;
- FOREIGN KEY
- může zapínat automatické triggery - ON UPDATE a ON DELETE;
- CHECK
V předchozích kapitolách jsme se o některých těchto omezeních zmínili, protože to bylo nutné pro logickou prezentaci materiálu, ale nyní se podrobněji podíváme na jejich syntaxi, použití a implementaci. Databázová omezení jsou dvou typů - založená na jednom poli a založená na více polích tabulky. Syntaxe obou typů omezení je uvedena níže.
= [CONSTRAINT constraint]
[ …]
= {UNIQUE | PRIMARY KEY
| CHECK ( )
| REFERENCES other_table [( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
}
Syntaxe omezení založených na více polích je následující:
= [CONSTRAINT constraint]
[< tconstraint> …]
= {{PRIMARY KEY | UNIQUE} ( col [, col …])
| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK ( )}
Rozdíl v syntaxi mezi omezeními založenými na jednom a na více polích je zřejmý - v posledním případě můžeme specifikovat několik polí zahrnutých do omezení. V případě omezení založeného na jednom poli se všechny popsané možnosti vztahují pouze na aktuální pole. Tyto dva typy omezení mají samozřejmě odlišný způsob použití: omezení založená na jednom poli se jednoduše přidají k definici požadovaného pole a omezení založená na více polích se specifikují za čárkou v obecné definici tabulky. Podrobné příklady jsou uvedeny v následujících částech této kapitoly.
Příklad typického omezení
Omezení založená na jednom poli jsou ve skutečnosti speciálním případem omezení založených na více polích.
Příklad vytvoření omezení primárního klíče pomocí těchto dvou různých přístupů je uveden níže. Vytvoříme tabulku obsahující pouze jedno pole a nastavíme na něj omezení primárního klíče.
Zde je příklad primárního klíče pomocí syntaxe omezení založeného na jednom poli:
CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); V tomto příkladu je pro pole ID_PK vytvořen primární klíč s názvem pktest. Výsledkem je poměrně kompaktní popis na jednom řádku. Pro stejný účel můžeme použít syntaxi omezení založených na více polích: CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));
Vytváření omezení
Podívejme se na vytváření omezení podrobněji. První v popisu obecné syntaxe omezení je možnost [CONSTRAINT constraint]. Jak vidíte, tato možnost je umístěna v hranatých závorkách, tj. je volitelná.
Pomocí této možnosti můžete nastavit název vytvářeného omezení, a to jak při použití syntaxe omezení založeného na jednom poli, tak v případě omezení založených na více polích. Pokud jste pro omezení nezadali název, InterBase jej automaticky vygeneruje. Přesto je lepší nastavit název vytvářenému omezení, aby se zlepšila čitelnost schématu databáze a zjednodušila se pozdější správa omezení.
Po nastavení názvu omezení je třeba definovat jeho typ. Podívejme se na různé typy omezení v pořadí, v jakém jsou uvedeny v popisu obecné syntaxe omezení.
Primární a jedinečné klíče
Primární klíče jsou jedním z hlavních typů databázových omezení. Používají se pro jednoznačnou identifikaci záznamů v tabulce. Předpokládejme, že v databázi ukládáme seznam lidí. Je docela možné, že zde budou dva (nebo více) lidé se stejným příjmením, jménem a patronymem. Jak můžeme rozlišit jednu osobu od druhé (samozřejmě jde o rozlišení jedné osoby od druhé podle informací uložených v databázi)?
V tomto případě je „osoba“ reprezentována jedním záznamem v tabulce, proto můžeme položit obecnější otázku - jak můžeme rozlišit jeden záznam v (jakékoli) tabulce od jiného záznamu ve stejné tabulce. K tomuto účelu se používají omezení - primární klíče. Primární klíč představuje jedno nebo několik polí v tabulce, jejichž kombinace je jedinečná pro každý záznam. Pro jednu tabulku neexistují opakující se hodnoty primárního klíče.
Jedinečné klíče plní stejnou funkci - také slouží k jednoznačné identifikaci záznamů v tabulce. Rozdíl mezi primárními a jedinečnými klíči je v tom, že v tabulce může být pouze jeden primární klíč, zatímco jedinečných klíčů může být několik. Je třeba poznamenat, že jak primární, tak jedinečný klíč lze použít jako referenční základ pro cizí klíče (viz dále).
Formální popis pojmů primární a jedinečný klíč, stejně jako další důležité definice, naleznete v příloze „Glosář“ na konci knihy. Syntaxe vytvoření primárního a jedinečného klíče založeného na jediném poli je následující:
< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}
Příklady primárních a jedinečných klíčů:
CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*primární klíč*/
uk1 VARCHAR(50) NOT NULL UNIQUE,/*jedinečný klíč */
uk2 INTEGER NOT NULL UNIQUE /\* další jedinečný klíč */);
Syntaxe vytvoření primárních a jedinečných klíčů založených na více polích:
= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Tato syntaxe umožňuje vytvářet klíče na základě kombinace polí. Zde jsou příklady vytvoření primárních a jedinečných klíčů z několika polí:
CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*primární klíč pkt založený na dvou polích*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*jedinečný klíč ukt1 založený na dvou polích*/
Všimněte si, že všechna pole zahrnutá do primárních a jedinečných klíčů musí být deklarována jako NOT NULL, protože tyto klíče nemohou mít nedefinovanou hodnotu. Kromě vytváření omezení primárních a jedinečných klíčů při vytváření tabulky existuje možnost přidat omezení do již existující tabulky. V tomto případě se používá příkaz DDL: ALTER TABLE. Syntaxe přidání omezení primárního nebo jedinečného klíče do existující tabulky je podobná výše popsané:
ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Podívejme se na příklad vytvoření primárního a jedinečného klíče pomocí ALTER TABLE:
CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));
Poté přidáme klíče. Nejprve primární:
ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);
Poté jedinečný: ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);
Je třeba poznamenat, že pouze vlastník této tabulky nebo systémový administrátor SYSDBA (podrobněji o vlastnících a uživateli SYSDBA viz kapitola „Bezpečnost v InterBase: uživatelé, jejich funkce a práva“ - část 4) může provádět přidávání (stejně jako mazání) primárních a jedinečných klíčů do tabulky.
Cizí klíče
Dalším často používaným omezením v databázích InterBase je omezení cizího klíče. Jedná se o velmi výkonný nástroj pro zajištění referenční integrity v databázi, který umožňuje nejen dohlížet na přítomnost správných referencí v databázi, ale také tyto reference automaticky řídit!
Smysl vytvoření cizího klíče je následující: pokud dvě tabulky slouží k ukládání vzájemně souvisejících informací, je nutné zaručit, že tento vztah bude vždy správný. Například dokument „nákladní list“ obsahující obecnou hlavičku (datum, číslo nákladního listu atd.) a sadu podrobných záznamů (popis zboží, množství atd.).
Pro uložení takového dokumentu se v databázi vytvoří dvě tabulky - jedna pro ukládání hlaviček nákladních listů a druhá pro ukládání obsahu nákladního listu - záznamů o zboží a jeho množství. Takové tabulky se nazývají hlavní a podřízená, nebo tabulka-master a detailní tabulka.
Podle zdravého rozumu nemůže obsah nákladního listu existovat bez přítomnosti jeho hlavičky. Jinými slovy, nemůžeme vložit záznam o zboží, pokud jsme nevytvořili hlavičku nákladního listu, a nemůžeme smazat záznam hlavičky, pokud existují záznamy o zboží. Pro realizaci takového chování se tabulka hlaviček a tabulka detailů spojují pomocí omezení cizího klíče.
Podívejme se na smysl nastavení omezení cizího klíče na příkladu tabulek obsahujících informace o nákladních listech. K tomuto účelu vytvoříme dvě tabulky pro uložení nákladního listu - tabulku TITLE pro uložení hlavičky a tabulku INVENTORY pro uložení informací o zboží zahrnutém v nákladním listu.
CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));
Všimněte si, že jsme v tabulce hlaviček okamžitě definovali primární klíč založený na poli ID_TITLE. Ostatní pole tabulky TITLE obsahují triviální informace o hlavičce nákladního listu - datum, číslo, komentář.
Nyní definujme tabulku pro ukládání informací o zboží zahrnutém do nákladního listu:
CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);
Podívejme se, jaká pole jsou zahrnuta v tabulce INVENTORY. Nejprve je to ID_INVENTORY - primární klíč této tabulky. Poté následuje celočíselné pole FK_TITLE sloužící jako reference na identifikátor ID_TITLE hlavičky v tabulce hlaviček nákladních listů. Poté následují pole ProductName, Kolvo a Positio popisující popis zboží, jeho množství a pozici v nákladním listu. Pole FK_TITLE je pro náš příklad nejdůležitější. Pokud chceme zobrazit informace o zboží určitého nákladního listu, měli bychom použít následující dotaz, ve kterém parametr mas_ID_TITLE definuje identifikátor hlavičky:
SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE
V popsané situaci v podstatě nic nebrání naplnění tabulky INVENTORY záznamy odkazujícími na neexistující záznamy v tabulce TITLE. Navíc nic nebrání smazání hlavičky již existujícího nákladního listu, kvůli čemuž by se záznamy o zboží mohly stát „bez vlastníka“. Server nezakáže provádění všech těchto vložení a odstranění. Kontrola integrity dat v databázi je tedy zcela ponechána na klientské aplikaci. Měli byste však vědět, že s jednou databází může pracovat několik aplikací, vyvinutých možná různými programátory, což může vést k rozdílné interpretaci dat a chybám. Proto je nezbytné nastavit explicitní omezení, že do tabulky INVENTORY lze vložit pouze takové záznamy o zboží, které mají správnou referenci na hlavičku nákladního listu. To je ve skutečnosti omezení cizího klíče, které umožňuje vkládat do polí zahrnutých do omezení pouze ty hodnoty, které jsou v druhé tabulce.
Takovýto omezující prvek lze vytvořit pomocí cizího klíče. Pro daný příklad musíme nastavit omezení cizího klíče pro pole FK_TITLE a svázat jej s primárním klíčem ID_TITLE v tabulce TITLE. Cizí klíč můžeme přidat k již existující tabulce následujícím příkazem:
ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)
Při přidávání cizího klíče se často objeví chyba - objekt se používá. Jde o to, že pro vytvoření cizího klíče musíme otevřít databázi v exkluzivním režimu - aby ve stejnou dobu nebyli žádní další uživatelé. Také bychom se neměli odkazovat na upravovanou tabulku - to může způsobit chybu objekt se používá.
Zde je INVENTORY název tabulky, pro kterou je omezení cizího klíče nastaveno; fktitle1 je název cizího klíče; FK_TITLE - pole tvořící cizí klíč; TITLE je název tabulky poskytující hodnoty (referenční základ) pro cizí klíč; ID_TITLE - pole primárního nebo jedinečného klíče v tabulce TITLE, která slouží jako referenční základ pro cizí klíč. Úplná syntaxe omezení cizího klíče (s možností vytvořit omezení založená na více polích) je uvedena níže:
= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Jak vidíte, definice obsahují velkou sadu možností. Pro začátek se podíváme na základní definici cizího klíče, která se nejčastěji používá v reálných databázích, a poté analyzujeme možné volby.
Deklarativní forma omezení cizího klíče se nejčastěji používá, když je specifikována sada polí (col [, col …]), která vytvoří omezení; stejně jako other_table, která obsahuje seznam možných hodnot pro cizí klíč v polích [(other_col [, other_col …])].
Zde je příklad takové definice při vytváření tabulky:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);
Všimněte si, že v této definici jsou klíčová slova FOREIGN KEY vynechána, stejně jako se předpokládá, že jediné pole FK_TITLE bude použito jako cizí klíč. Úplnější forma vytvoření cizího klíče současně s tabulkou je uvedena v následujícím příkladu:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);
Použití NULL v polích cizího klíče
V polích, na jejichž základě je vytvořen cizí klíč, je povoleno používat NULL- pole. Tato možnost je přidána pro umožnění vzájemných odkazů. Například pokud existují dvě tabulky, které se na sebe odkazují pomocí cizích klíčů. Pokud v těchto cizích klíčích nepovolíme prázdný odkaz (tj. NULL), nebude možné přidat žádný záznam do propojených tabulek: pro přidání záznamu do první tabulky je vyžadován záznam ve druhé tabulce a naopak.
Použití NULL jako prázdného odkazu umožňuje vytvářet vzájemné odkazy dvou vzájemně odkazujících se tabulek a také ukládat hierarchické struktury v relačních tabulkách - přičemž kořenové uzly odkazují na „prázdné" záznamy (tj. jednoduše obsahují NULL).
Rozšířené možnosti podpory referenční integrity pomocí cizího klíče
Obvykle deklarativní varianta omezení cizího klíče zcela postačuje, server pouze hlídá, aby nebylo možné vložit nesprávné hodnoty do tabulky s cizím klíčem, nebo - při pokusu o to - se objeví chyba. InterBase ale umožňuje provádět sadu automatických operací při změně / mazání cizího klíče. Pro tento účel se používá následující sada voleb cizího klíče:
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Tyto volby umožňují definovat různé operace při aktualizaci nebo mazání hodnot cizího klíče.
Můžeme například nastavit, že při mazání primárního klíče v nadřazené tabulce budou smazány všechny záznamy se stejným cizím klíčem v podřízené tabulce. V tomto případě musíme definovat cizí klíč následujícím způsobem:
ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE
Pro implementaci těchto operací se ve skutečnosti používá systémový trigger, který provádí určité operace. V tabulce 1.2 je popsáno fungování různých voleb (všimněte si, že volby NO ACTION|CASCADE|SET DEFAULT|SET NULL nelze použít v jedné větě ON XXX).
Tabulka 1.2
| Událost | Operace | |||
| NO ACTION | CASCADE | SET DEFAULT | SET NULL | |
| ON DELETE | Při mazání cizího klíče nedělat nic - používá se ve výchozím nastavení | Při mazání smazat všechny související záznamy z podřízené tabulky | Při změně nastavit pole cizího klíče na výchozí hodnotu |
Při změně nastavit pole cizího klíče na NULL |
| ON UPDATE | Při změně nedělat nic - používá se ve výchozím nastavení | Při změně záznamu změnit všechny související záznamy v podřízené tabulce | Při mazání nastavit pole cizího klíče na výchozí hodnotu |
Při mazání nastavit pole cizího klíče na NULL |
Pokud nezadáme nic nebo zadáme NO ACTION, musíme se o změnu cizího klíče (v případě změny primárního klíče) postarat sami a při mazání primárního klíče musíme předem smazat záznamy z podřízené tabulky. Buďte velmi opatrní při použití volby CASCADE: její neopatrné použití může vést ke smazání velkého počtu souvisejících záznamů.
Omezení CHECK
Jedním z nejužitečnějších omezení v databázi je omezení check. Jeho funkce je velmi jednoduchá - zkontrolovat hodnotu vkládanou do tabulky podle nějaké podmínky a podle splnění této podmínky data vložit nebo ne. Jeho syntaxe je poměrně jednoduchá:
= [CONSTRAINT constraint] CHECK ( )}
Zde constraint je název omezení; je vyhledávací podmínka, ve které může být vkládaná / aktualizovaná hodnota použita jako parametr. Pokud je vyhledávací podmínka splněna, je povoleno vložit / aktualizovat tuto hodnotu, pokud není - objeví se chyba. Nejjednodušší příklad check:
create table checktst( ID integer CHECK(ID>0));
Tento check určuje, zda je vkládaná / aktualizovaná hodnota pole ID větší než nula, a podle výsledku umožňuje vložit / aktualizovat novou hodnotu nebo informovat o chybě (viz kapitola „Rozšířené možnosti jazyka uložených procedur InterBase" (část 1)).
Existují také složitější varianty checků. Úplná syntaxe vyhledávací podmínky je následující:
= {
{ | ()}
| [NOT] BETWEEN AND
| [NOT] LIKE [ESCAPE ]
| [NOT] IN ( [ , …] | )
| IS [NOT] NULL
| {[NOT] {= | < | >} | >= | <=}
{ALL | SOME | ANY} ()
| EXISTS ( )
| SINGULAR ( )
| [NOT] CONTAINING
| [NOT] STARTING [WITH]
| ()
| NOT
| OR
| AND }
CHECK tedy poskytuje velkou sadu možností pro kontrolu vkládaných / aktualizovaných hodnot. Při použití CHECK byste měli pamatovat na následující omezení:
- Data pro CHECK se berou pouze z aktuálního záznamu. Neměli byste brát data pro výraz v CHECK z jiných záznamů stejné tabulky - mohou být změněna jinými uživateli
- Pole může mít pouze jedno omezení CHECK
- Pokud je pro definici pole použita doména s omezením CHECK domény, nelze ji znovu definovat na úrovni konkrétního pole v tabulce. Je třeba říci, že CHECK jsou implementovány systémovými triggery, proto musíme být opatrnější při používání velmi dlouhých podmínek, které mohou výrazně zpomalit procesy vkládání a aktualizace záznamů.
Mazání omezení
Velmi často odstraňujeme různá omezení z nejrůznějších důvodů. Pro odstranění omezení bychom měli použít příkaz ALTER TABLE následujícího tvaru: ALTER TABLE tablename DROP CONSTRAINT constraintname
constraintname je název omezení, které má být odstraněno. Pokud byl při vytváření omezení zadán určitý název, měli bychom jej použít, ale pokud ne, musíme otevřít některý nástroj pro správu InterBase, vyhledat všechna omezení s ním související a zjistit, jaký systémový název InterBase vygeneroval pro požadované omezení.
Je třeba poznamenat, že omezení mohou odstranit pouze vlastník tabulky nebo systémový administrátor SYSDBA.