• Zobrazit
• Upravit
• Historie
• Tisk

Referenční integrita

Aby všechny odkazy cizích klíčů fungovaly, o to se stará omezení referenční integrity. Toto omezení znamená, že ve sloupci (nebo kombinaci sloupců), kde odkaz má být, smí být buď NULL hodnota (či kombinace NULL hodnot), nebo hodnota (nebo kombinace hodnot), která se v odpovídajícím sloupci (či kombinaci sloupců) odkazované tabulky skutečně vyskytuje. Odkazy prostě nesmí být "doprázdna".

---

Při vkládní dat do pole (či kombinace polí), které je definováno jako cizí klíč, DBMS kontroluje, zda odkazovaný objekt v databázi existuje. Pokud ne, požadovaná transakce se neprovede. Tomu se říká omezení referenční integrity. V definici omezení referenční integrity je dále třeba určit, co se má dít při pokusu o smazání záznamu, na nějž existuje odkaz cizího klíče.

---

Vysvětlení vám podá následující příklad:

Příklad:
Uvažujme komunitní web, s registrovanými uživateli a tématy. Jednotlivá témata mají jednotlivě určené správce z řad uživatelů, jedno téma jednoho správce. V tabulce TEMA vytvoříme sloupec SPRAVCE, kde očekáváme identifikátor uživatele spravujícího dané téma:

Protože má být možno kterémukoli uživateli okamžitě zrušit členství (například pokud poruší pravidla komunity), může se stát, že některé téma "osiří", jeho správce přestane existovat. Pak se do sloupce SPRAVCE zapíše prázdná hodnota NULL. SQL kód, který toto vše vyřeší:

    create table UZIV (
    IDUZ int,
    LOGIN VARCHAR2(30)not null,
    PASSWD VARCHAR2(50),
    constraint PK_UZIV primary key (IDUZ),
    constraint AK_UZIV unique (LOGIN);

    create table TEMA (
    IDTEM int,
    NAZTEM VARCHAR2(100),
    SPRAVCE int,
    constraint PK_TEMA primary key (IDTEM),
    constraint AK_TEMA unique (NAZTEM),
    constraint FK_TEMA_SPRAVCE foreign key (SPRAVCE) references UZIV (IDUZ) on delete set null);

Pokusíte-li se vložit nové téma s hodnotou v poli SPRAVCE, která není identifikátorem žádného uživatele, nastane chyba:

    insert into TEMA (IDTEM,NAZTEM,SPRAVCE) values (1,'Jak jíst slanečky',1);

To proto, že v tabulce UZIV žádný uživatel s identifikátorem 1 není. Napravme to:

    insert into UZIV (IDUZ,LOGIN,PASSWD) values (1,'sGurman','rys1on');

A pak zkusme znovu:

    insert into TEMA (IDTEM,NAZTEM,SPRAVCE) values (1,'Jak jíst slanečky',1);

(O výsledku se přesvědčte příkazy SELECT.) Úspěšně jsme vložili do každé tabulky jeden řádek, a cizí klíč SPRAVCE "funguje", ukazuje na exitujícího uživatele. Ukažme si, jak funguje on delete set null, které bylo součástí definice naší refrenční integrity. Smažme uživatele sGurman:

    delete from UZIV where LOGIN='sGurman';

Podívejte se, co je nyní v tabulách UZIV i TEMA. Uživatel s IDUZ=1 tam není, v záznamu tématu 'Jak jíst slanečky' je v poli SPRAVCE práznaná hodnota.

V daném komunitním webu se dále evidují uživatelé přihlášení k jednotlivým tématům. Přitom jeden uživatel může být přihlášen k více tématům, a k jednomu tématu může být přihlášeno více uživatelů:

Pokud bude smazán některý uživatel, automaticky se mají smazat jeho prihlášky ke všem tématům, ke kterým je přihlášen. Občas se také mažou stará témata, ale smí se smazat pouze ta, ke kterým již nikdo přihlášen není (přihlášení, která nebyla použita více než půl roku, se dávkově každý den ruší). SQL kód, který zajistí takovéto fungování referenční integrity:

    create table PRIHL (
    UZIV int,
    TEMA int,
    LAST date default sysdate,
    constraint PK_PRIHL primary key (UZIV,TEMA),
    constraint FK_PRIHL_KDO foreign key (UZIV) references UZIV (IDUZ) on delete cascade,
    constraint FK_PRIHL_KAM foreign key (TEMA) references TEMA (IDTEM));

Vyzkoušejme, jak to funguje. Vložme nového uživatele 'pojidac' a přihlásíme ho k tématu 'Jak jíst slanečky':

    insert into UZIV (IDUZ,LOGIN,PASSWD) values (2,'pojidac','dQas4B');

    insert into PRIHL (UZIV,TEMA) values (2,1);

(Přesvědčte se, co má tato přihláška v poli LAST.) Pokusme se nyní smazat téma 'Jak jíst slanečky':

    delete from TEMA where NAZTEM='Jak jíst slanečky';

Nejde to, protože existuje přihláška k tomuto tématu. Zato když smažeme uživatele 'pojidac', smažou se i všechny jeho přihlášky:

    delete from UZIV where LOGIN='pojidac';

Přesvědčte se, co v tabulkách zbylo. Pak opět zkuste smazat téma 'Jak jíst slanečky'. Přesvědčte se, co v tabulkách zbylo. (Až všechny pokusy skončíte, nezapomeňte po sobě uklidit – zrušit tabulky!)

---

Varianty referenční integrity ohledně mazání master záznamů: restriktivní (defaultní varianta) Nelze smazat master záznam, pokud na něj existuje odkaz z nějakéhého slave záznamu. set null Pokud smažeme máster záznam, v slave záznamu se hodnota cizího klíče nastaví na NULL. kaskádová Pokud smažeme master záznam, smažou se i všechny slave záznamy odkazující na něj.

Poznámka: Varianty on update mnoho DBMS ani nepodporuje. Praxe ukázala nevhodnost jejich užití.

---

Zrušení omezení referenční integrity

Pokud zrušíme tabulku, zruší se i všechna omezení na ní definovaná. Pokud ovšem tabulka je master pro nějaké omezení referenční integrity definované na jiné slave tabulce, nelze tuto master tabulku zrušit. Nejprve je nutno zrušit ono omezení referenční integrity.

Příklad:
Definujme dvě tabulky, vzájemně svázané omezením referenční integrity:

    create table ODDELENI (
    CISLO_ODDELENI int,
    NAZEV_ODDELENI varchar(50),
    VEDOUCI_ODDELENI int,
    constraint PK_ODDEL primary key (CISLO_ODDELENI),
    costraint NAZEV_ODDELENI not null unique);

    create table ZAMESTNANEC (
    CISLO_ZAMESTNANCE int,
    JMENO varchar(100),
    PRIJMENI varchar(100),
    CISLO_ODDELENI int,
    constraint PK_ZAM primary key (CISLO_ZAMESTNANCE),
    costraint FK_PRACOVISTE foreign key CISLO_ODDELENI references ODDELENI);

    alter table ODDELENI add constraint FK_VEDOUCI foreign key VEDOUCI_ODDELENI references ZAMESTNANEC on delete set null;

Druhé z těchto omezení referenční integrity bylo možné definovat až po definici druhé tabulky, protože dříve odkazovaná tabulka neexistovala. Uvažujme situaci, kdy tuto dvojici tabulek později chceme smazat. Pokusíme se smazat jednu, event. druhou:

    drop table ODDELENI;

Toto je interpretovaná odpověď systému ORACLE: "ORA-02449: jedinečný/primární klíč v tabulce, na kterou odkazují cizí klíče". Tabulka ODDELENI se nezrušila. Pokus o zrušení druhé tabulky – drop table ZAMESTNANEC; – přinese analogický výsledek. Řešením je nejprve zrušit omezení referenční integrity:

    alter table ODDELENI drop constraint FK_VEDOUCI;
    alter table ZAMESTNANEC drop constraint FK_PRACOVISTE;

A pak lze tabulky zrušit:

    drop table ODDELENI;
    drop table ZAMESTNANEC;