• 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;