Referenční integrita dvojice tabulek

Referenční integrita

Referenční integrita je nástroj, který pomáhá udržovat vztahy mezi záznamy v relačně propojených tabulkách.

Definuje se jako vztah mezi dvojici tabulek. Tabulka, v níž je pravidlo referenční integrity uvedeno, se nazývá podřízená tabulka. Tabulka, jejíž jméno je v integritním omezení uvedeno, je nadřízená tabulka. V nadřízené i podřízené tabulce je určen sloupec nebo skupina sloupců tvořící klíč. Pravidlo referenční integrity vyžaduje, aby pro každý záznam v podřízené tabulce, jehož klíč nemá hodnotu NULL, existoval v nadřízené tabulce záznam se stejným klíčem. Klíč v nadřízené tabulce musí být unikátním indexem, klíč v podřízené tabulce musí být nějakým indexem.

Při manipulaci s daty se referenční integrita projevuje takto:

Přidáváte-li záznam do podřízené tabulky (resp. přepisujete-li hodnotu sloupce z klíče), kontroluje se existence stejného klíče v nadřízené tabulce.
Při smazání hodnoty sloupce z klíče (zrušením záznamu nebo přepsáním hodnoty) v nadřízené tabulce se kontroluje, zda v podřízené tabulce není záznam se stejnou hodnotou klíče.

Porušení pravidla 1. vyvolá chybu a odvolání transakce. Porušení pravidla 2. může buď vyvolat chybu a odvolání transakce, nebo úpravu dat v podřízené tabulce v souladu s definovanými akcemi.

Pravidlo referenční integrity se specifikuje takto:

referenční_integritní_omezení ::= FOREIGN KEY (jméno_sloupce {, jméno_sloupce}... ) 
	REFERENCES [schéma.]jméno_tabulky [ jméno_sloupce {, jméno_sloupce}... ] 
	[ ON UPDATE akce_referenční_integrity ] [ ON DELETE akce_referenční_integrity ] 
	[ vyhodnocení ]
akce_referenční_integrity ::=  SET NULL | SET DEFAULT | CASCADE | NO ACTION

První skupina jmen sloupců určuje klíč v podřízené tabulce. Druhá skupina určuje klíč v nadřízené tabulce. Pokud není uvedena, pak se použije primární klíč nadřízené tabulky. Klíče v obou tabulkách musí být stejného typu.

Pravidlo referenční integrity propojující jeden sloupec s jedním sloupcem lze alternativně popsat jako vlastnost sloupce.

Aktivní referenční integrita

V pravidle referenční integrity lze specifikovat, co má server provést, pokud by v důsledku zrušení nebo přepsání záznamu v nadřízené tabulce mělo dojít k porušení referenční integrity. V takovém případě se provede zadaná akce referenční integrity uvedená v klauzuli ON DELETE resp. ON UPDATE:

SET NULL - v záznamech podřízené tabulky se stejnou hodnotou klíče se do sloupců tvořících klíč zapíše hodnota NULL;
SET DEFAULT - v záznamech podřízené tabulky se stejnou hodnotou klíče se do sloupců tvořících klíč zapíše implicitní hodnota určena v definici tabulky;
CASCADE - změna provedena v nadřízené tabulce se provede i na záznamech podřízené tabulky se stejným klíčem: při zrušení nadřízeného záznamu se zruší podřízené záznamy, při změně hodnot klíče v nadřízeném záznamu se stejná změna provede i v podřízených záznamech.
NO ACTION nebo nespecifikovaná akce - dojde k chybě sqlstate 40006 (REFERENTIAL_CONSTRAIN), která způsobí odvolání transakce.

Ve všech těchto případech tedy server zamezí porušení pravidla referenční integrity.

Při provádění akcí referenční integrity se nekontrolují práva k provádění změn v podřízené tabulce. Akce se provede, pokud klient má právo provést změnu, která akci vyvolává, v nadřazené tabulce.

Spouštění triggerů prováděním akcí referenční integrity je závislé na nastavení příznaků kompatibility pomocí funkce Set_sql_option (SQLOPT_NO_REFINT_TRIGGERS (4096)). Pokud se triggery spouštějí, pak během provádění triggeru lze rozpoznat jeho vyvolání kvůli referenční integritě pomocí systémové proměnné @@ACTIVE_RI_IN_PROGRESS.

Příklad: tabulka s podmínkou na číslo >0 a aktivní referenční integritou do číselníku firem (nadřízená tabulka FIRMY)

CREATE TABLE `OBJEDNAVKY` (
`FIRMA_ID` INTEGER ,
`OBJEDNANO` NUMERIC(14,2) ,
...
CONSTRAINT `Firmy` INDEX (`FIRMA_ID`),
CONSTRAINT `Check_gt0` CHECK (objednano>0) INITIALLY IMMEDIATE,
CONSTRAINT `Firma-Obj` FOREIGN KEY (`FIRMA_ID`) REFERENCES `FIRMY`(`FIRMA_ID`) 
	ON UPDATE CASCADE ON DELETE CASCADE)

Kdy se kontroluje dodržení integritních omezení?

Server může kontrolovat dodržení referenční integrity buď na konci příkazu nebo na konci transakce. Čas vyhodnocení integritního omezení předepisuje klauzule INITIALLY IMMEDIATE nebo INITIALLY DEFERRED.

Integritní omezení v tabulce

Databázové tabulky

Kdy se kontroluje dodržení integritních omezení