5. CVIČENÍ

Druhy integrity dat

• entitní
• doménová
• referenční
  • restriktivní
  • kaskádní

Entitní integrita spočívá v tom, že žádný atribut který je součástí primárního klíče nesmí být NULL, aby bylo možné každý záznam jednoznačně identifikovat. Klíč označíme klíčovým slovem PRIMARY KEY.

CREATE TABLE osoby (
  os_cislo   NUMBER(5)    PRIMARY KEY,
  jmeno      VARCHAR2(30) NOT NULL,
  prijmeni   VARCHAR2(30) NOT NULL,
  dat_naroz  DATE,
  pohlavi    CHAR(1)      NOT NULL,
  telefon    VARCHAR2(50)
);

Klíč může být i složený, tj. může jej tvořit několik atributů (sloupců) :

CREATE TABLE predmety (
  zkratka   VARCHAR2(5),
  katedra   VARCHAR2(3),
  nazev     VARCHAR2(30) NOT NULL,
  kredity   NUMBER(2)    NOT NULL,
  CONSTRAINT pk_predmety PRIMARY KEY (zkratka, katedra)
);

Ostatní jedinečné položky, které nejsou primárním klíčem lze označit klíčových slovem UNIQUE :

CREATE TABLE osoby (
  os_cislo   NUMBER(5)    PRIMARY KEY,
  rod_cis    VARCHAR2(30) NOT NULL UNIQUE,
  jmeno      VARCHAR2(30) NOT NULL,
  prijmeni   VARCHAR2(30) NOT NULL,
  dat_naroz  DATE,
  pohlavi    CHAR(1)      NOT NULL,
  telefon    VARCHAR2(50)
);

Doménová :

Doménová integrita zajišťuje, že hodnota atributu leží vždy v korektním oboru hodnot. Obor hodnot atributu lze kontrolovat pomocí klauzule CHECK(podmínka).

CREATE TABLE osoby (
  os_cislo   NUMBER(5)    PRIMARY KEY,
  rod_cis    VARCHAR2(30) NOT NULL UNIQUE,
  jmeno      VARCHAR2(30) NOT NULL,
  prijmeni   VARCHAR2(30) NOT NULL,
  dat_naroz  DATE,
  pohlavi    CHAR(1)      NOT NULL,
  telefon    VARCHAR2(50),
  plat       NUMBER(5)    CHECK ( plat > 5000 AND plat < 20000 )
);

Referenční :

Obsahuje-li tabulka cizí klíč, musí v existovat v "domovské" tabulce záznam se stejnou hodnotou primárního klíče. V opačném případě musí všechny atributy tvořící cizí klíč mít hodnotu NULL.

Pozn.: definici integritních omezení není nutné zapisovat přímo u definice sloupce, ale je možné ji zapsat až na konec jako klauzuli CONSTRAINT. Jednotlivá integritní omezení je možné i pojmenovat.

"Rodičovská" tabulka :

CREATE TABLE pracoviste (
  cislo_prac NUMBER(5)    NOT NULL,
  nazev      VARCHAR2(30) NOT NULL UNIQUE,
  CONSTRAINT pk_prac PRIMARY KEY ( cislo_prac )
);

Následující příklad ukazuje restriktivní způsob zajištění referenční integrity. Nelze zrušit pracoviště na němž jsou evidováni zaměstanci.

Tabulka s cizím klíčem :

CREATE TABLE osoby (
  os_cislo   NUMBER(5)    NOT NULL,
  rod_cis    VARCHAR2(30) NOT NULL UNIQUE,
  jmeno      VARCHAR2(30) NOT NULL,
  prijmeni   VARCHAR2(30) NOT NULL,
  dat_naroz  DATE,
  pohlavi    CHAR(1)      NOT NULL,
  telefon    VARCHAR2(50),
  plat       NUMBER(5)    CHECK ( plat > 5000 AND plat < 20000 ),
  cislo_prac NUMBER(5)    NOT NULL,
  CONSTRAINT pk_osoby PRIMARY KEY ( os_cislo ),
  CONSTRAINT fk_prac  FOREIGN KEY ( cislo_prac )
                      REFERENCES pracoviste ( cislo_prac )
                      ON UPDATE NO ACTION
                      ON DELETE NO ACTION
);

Kaskádní způsob zajištění referenční integrity zajišťuje smazání záznamů všech zaměstnanců rušeného pracoviště.

Tabulka s cizím klíčem :

CREATE TABLE osoby (
  os_cislo   NUMBER(5)    NOT NULL,
  rod_cis    VARCHAR2(30) NOT NULL UNIQUE,
  jmeno      VARCHAR2(30) NOT NULL,
  prijmeni   VARCHAR2(30) NOT NULL,
  dat_naroz  DATE,
  pohlavi    CHAR(1)      NOT NULL,
  telefon    VARCHAR2(50),
  plat       NUMBER(5)    CHECK ( plat > 5000 AND plat < 20000 ),
  cislo_prac NUMBER(5)    NOT NULL,
  CONSTRAINT pk_osoby PRIMARY KEY ( os_cislo ),
  CONSTRAINT fk_prac  FOREIGN KEY ( cislo_prac )
                      REFERENCES pracoviste ( cislo_prac )
                      ON DELETE CASCADE
                      ON UPDATE CASCADE
);

• SET NULL - hodnoty cizího klíče jsou nastaveny na NULL
• SET DEFAULT - hodnoty cizího klíče jsou nastaveny na implicitní hodnotu (uvedenou u sloupce v příkazu CREATE TABLE)

• Přidávání integritních omezení :

ALTER TABLE osoby
  ADD CONSTRAINT pk_osoby PRIMARY KEY ( os_cislo );

ALTER TABLE osoby
  ADD CONSTRAINT fk_prac FOREIGN KEY ( cislo_prac )
                         REFERENCES pracoviste ( cislo_prac )
                         ON DELETE CASCADE;

• Rušení integritních omezení :

ALTER TABLE osoby
  DROP CONSTRAINT fk_prac;

ALTER TABLE osoby
  DROP CONSTRAINT pk_prac;