Referenční integrita

Když databáze odkazuje na něco, co neexistuje

Máme naši školní databázi:

TRIDA → ZAK → ZAPIS ← PREDMET

Například:

ZAK

TRIDA

V tabulce zak je:

trida_id = 1

Databáze tím říká:

Tento žák patří do třídy s ID 1.

Co kdyby někdo udělal chybu?

Přidáme:

Jenže:

třída 99 neexistuje.

Eva tedy patří do neexistující třídy.

Takový záznam by byl chybný.

Referenční integrita

Referenční integrita hlídá, aby odkazy mezi tabulkami vždy vedly na existující záznam.

Pokud máme:

trida_id = 1

musí v tabulce trida skutečně existovat:

trida_id = 1

Kdo koho hlídá?

Používáme dva typy klíčů:

Primární klíč – PRIMARY KEY

Jednoznačně označuje záznam.

Například:

trida.trida_id

Cizí klíč – FOREIGN KEY

Odkazuje na záznam v jiné tabulce.

Například:

zak.trida_id

tedy:

FOREIGN KEY → PRIMARY KEY

Jak to funguje v naší databázi?

Máme:

TRIDA

trida_id

↓

ZAK

trida_id

Žák tedy může mít pouze takové trida_id, které skutečně existuje v tabulce trida.

Stejně funguje:

ZAK → ZAPIS

a

PREDMET → ZAPIS

Co databáze nedovolí?

Máme předměty:

A zkusíme vytvořit zápis:

Předmět číslo 50 neexistuje.

Databáze by měla zápis odmítnout.

To je právě referenční integrita.

Sirotek v databázi

Představ si tento zápis:

Jan Novák → predmet_id 2

A potom někdo předmět číslo 2 smaže.

Zůstane:

Jan Novák → predmet_id 2

ale předmět 2 už neexistuje.

Takovému záznamu se někdy říká osiřelý záznam.

Databáze tomu musí zabránit.

Co se stane při mazání?

Máme:

PREDMET

2 → Počítačové sítě

a v tabulce zapis:

Jan → predmet_id 2

Petra → predmet_id 2

Co se má stát, když chceme předmět smazat?

Databáze může mít několik pravidel.

RESTRICT

Nedovol smazání.

Pokud se předmět někde používá, databáze řekne:

Nelze smazat. Tento záznam používají jiné tabulky.

To je často nejbezpečnější varianta.

CASCADE

Smaž také navázané záznamy.

Smažeme předmět:

Počítačové sítě

Databáze automaticky smaže také všechny zápisy tohoto předmětu.

Pozor

CASCADE je užitečné, ale může smazat velké množství dat.

SET NULL

Další možnost:

odkaz se nastaví na:

NULL

Například:

trida_id = NULL

To znamená:

Žák momentálně není přiřazen do žádné třídy.

Toto lze použít pouze tam, kde NULL dává smysl.

Tři základní možnosti

Jak to známe z SQL?

Cizí klíč můžeme vytvořit například:

FOREIGN KEY (trida_id)
REFERENCES trida(trida_id)

Tím databázi říkáme:

trida_id v tabulce žák musí existovat v tabulce trida.

Příklad

CREATE TABLE zak (
    zak_id SERIAL PRIMARY KEY,
    jmeno VARCHAR(50),
    prijmeni VARCHAR(50),
    trida_id INTEGER,
    
    FOREIGN KEY (trida_id)
    REFERENCES trida(trida_id)
);

Důležitá část je:

FOREIGN KEY (trida_id)
REFERENCES trida(trida_id)

Referenční integrita v našem modelu

Naše databáze má například tyto vazby:

TRIDA

trida_id

↓

ZAK

trida_id

↓

ZAPIS

zak_id

↑

PREDMET

predmet_id

Referenční integrita hlídá, aby žádná šipka neukazovala na neexistující záznam.

Databázová výzva

Máme tato data:

TRIDA

ZAK

Najdi chybu.

Otázky

1. Který záznam porušuje referenční integritu?

2. Proč?

3. Jaké hodnoty může mít trida_id?

4. Co by měla databáze udělat při vložení trida_id = 7?

Druhá výzva

Máme předmět:

1 → Databáze

a zápisy:

Jan → Databáze

Petra → Databáze

Co by se mělo stát, když někdo předmět Databáze smaže?

Vyber:

A) RESTRICT

Smazání zakázat.

B) CASCADE

Smazat předmět i všechny jeho zápisy.

C) SET NULL

Ponechat zápisy bez předmětu.

Diskutuj, která varianta dává v tomto případě největší smysl.

Co si zapamatovat

PRIMARY KEY

Kdo jsem?

Jednoznačně označuje záznam.

FOREIGN KEY

Na koho odkazuji?

Propojuje tabulky.

REFERENČNÍ INTEGRITA

Hlídá, aby odkaz vždy vedl na existující záznam.

Jednoduše:

Databáze nesmí ukazovat na něco, co neexistuje.

A právě díky referenční integritě zůstávají propojené tabulky konzistentní a spolehlivé.