Příkazy DDL

Ještě předtím, než jsme začali používat databázový systém MariaDB, jsem vám každému vytvořil vlastní databázi. Na to jsem použil příkaz (příklad):

CREATE DATABASE db22brynych CHARACTER SET utf8 COLLATE utf8_czech_ci;

Tento příkaz vytvořil novou databázi s názvem db22brynych, ve které se bude používat znaková sada utf8 a řazení české case insensitive.

Vytváření relací

Relace budeme vytvářet podle logického schématu / diagramu tříd tak, že začínáme od těch relací, kde nejsou cizí klíče. Například ve schématu:

začneme nejprve s vytvořením relace Znacky, protože tam není žádný cizí klíč (FK v prvním sloupci). Relaci Znacky vytvoříme příkazem:

CREATE TABLE Znacky (
  znacka char(2) PRIMARY KEY,
  nazev varchar(64) NOT NULL,
  zeme char(2) NOT NULL
);

Vytvoření relace Bus už bude o trochu složitější, protože tam musíme uvést také vazbu (na jaký PK se FK znacka odkazuje):

CREATE TABLE Bus(
  IDVozu int PRIMARY KEY,
  barva char(1) NOT NULL,
  znacka char(2) NOT NULL,
  datumNakupu date NOT NULL,
  datumVyrazeni date NULL,
  CONSTRAINT FK_BusZnacka FOREIGN KEY (znacka) REFERENCES Znacky(znacka)
);

Pojďme si nyní trochu vysvětlit poslední řádek příkazu:

• CONSTRAINT = klíčové slovo pro integritní omezení
• FK_BusZnacka = unikátní název integritního omezení (unikátní v celé databázi)
• FOREIGN KEY = klíčové slovo určující druh integritního omezení (referenční; cizí klíč)
• (znacka) = název atributu ve vytvářené relaci (Bus) – musí být v závorce
• REFERENCES = klíčové slovo před uvedením, kam cizí klíč odkazuje
• Znacky(znacka) = relace, na kterou se FK odkazuje (Znacky) a atribut v této relaci (znacka)

Ještě před koncem příkazu můžeme specifikovat, jakým způsobem se bude referenční integritní omezení chovat – defaultní nastavení je ON DELETE RESTRICT, ON UPDATE RESTRICT. Tj. vrať chybu, jestliže se někdo pokusí vymazat záznam, který je dále využíván. V některých případech ale můžeme chtít s vaničkou vylít i dítě, například relace Objednavky a PolozkyObjednavky:

Zde bychom relace vytvořili například takto:

CREATE TABLE Objednavky( 
  IDObj int PRIMARY KEY AUTO_INCREMENT, 
  datumObj date DEFAULT NOW(), 
  zakaznik varchar(64), 
  status int DEFAULT 0
);
CREATE TABLE PolozkyObjednavky(
  IDPol int AUTO_INCREMENT,
  IDObj int,
  sluzba varchar(64),
  cena decimal(10,2),
  PRIMARY KEY (IDPol, IDObj),
  CONSTRAINT FK_Obj FOREIGN KEY (IDObj) REFERENCES Objednavky(IDObj) 
  ON DELETE CASCADE
);

Pojďme si nyní zdůraznit několik nově používaných parametrů:

• AUTO_INCREMENT = používáme k tomu, aby SŘBD automaticky přiřadil nově vytvářenému záznamu číslo o jedničku vyšší než naposledy přiřazené číslo
• DEFAULT NOW() = můžeme použít k tomu, aby hodnota nově přidávaného atributu byla ve výchozím stavu rovná dnešnímu datu
• PRIMARY KEY (IDPol, IDObj) = zde používáme složený primární klíč, je složený ze dvou atributů (IDPol, IDObj) – v tomto případě je potřeba zapsat jej až za definici obou atributů
• ON DELETE CASCADE = jakmile smažeme hlavičku objednávky (záznam z relace Objednavky), smažou se automaticky i všechny položky objednávky.

Změna relací

Zkusme si nejprve ukázat, jakým způsobem bychom mohli přidat atribut do již existující relace. V případě přidávání atributu většinou nehrozí konflikt s pravidly (viz 12 pravidel E. F. Codda), čili můžeme začít. Představme si, že v relaci Bus nám chybí atribut pocetSedadel, který by měl být datového typu kladné celé číslo. Přidáme jej tedy:

ALTER TABLE Bus ADD pocetSedadel INT UNSIGNED NULL;

Pojďme si příkaz opět rozebrat:

• ALTER TABLE = příkaz pro změnu strukturu databázové tabulky
• Bus = název měněné relace
• ADD = klíčové slovo pro přidání (též ADD COLUMN)
• pocetSedadel = název nově přidávaného atributu
• INT = datový typ
• UNSIGNED = bez znaménka, tj. pouze kladná čísla
• NULL = nepovinný atribut

Jestliže v relaci již jsou záznamy, je nutné u nově přidaného atributu nastavit buďto NULL nebo DEFAULT hodnotu, jinak bychom porušili pravidlo 9: Pravidlo fyzické a logické nezávislosti dat.

Přidejme ještě jeden atribut, například mistoProKocar, kam se bude ukládat, jestli autobus má místo pro kočárek:

ALTER TABLE Bus ADD mistoProKorac BOOLEAN DEFAULT FALSE;

Nyní jsme si ale všimli, že v názvu atributu máme chybu. Bude potřeba tedy existující atribut změnit. To uděláme pomocí příkazu:

ALTER TABLE Bus CHANGE mistoProKorac mistoProKocar BOOLEAN DEFAULT FALSE;

Všimněte si, že nejprve identifikujeme atribut, který budeme měnit (mistoProKorac) a poté celou definici atributu musíme uvést znovu. Pomocí stejného příkazu je možné měnit i datový typ – ale pozor – nový datový typ musí vyhovovat již existujícím datům, jinak nám změnu SŘBD neprovede (porušili bychom pravidlo 9: Pravidlo fyzické a logické nezávislosti dat).

Posledním základním příkazem na změnu struktury je odebrání atributu. Pojďme si tedy změněný atribut z relace odebrat:

ALTER TABLE Bus DROP mistoProKocar;

Výmaz relace

Výmaz relace probíhá už velmi jednoduše – ale opět pozor na existující data. Relace neobsahující vazbu na cizí klíče se bude mazat dobře. Ale jestliže relace obsahuje data, na která se vážou data v druhé relaci, bude potřeba nejprve smazat druhou relaci.

V našem případě tedy můžeme smazat nejprve relaci Bus a poté teprve Znacky:

DROP TABLE Bus;
DROP TABLE Znacky;

Role SŘBD během DDL příkazů

Zatímco uživatel zadává (nebo aplikace zasílá do databáze) jednoduchý příkaz (například CREATE TABLE), v pozadí se odehrává řada složitějších procesů. Systém řízení báze dat (SŘBD) provádí například následující akce:

1. řízení oprávnění
2. aktualizace systémového katalogu
3. vytvoření fyzických datových struktur

Před provedeném jakéhokoliv DDL příkazu SŘBD vždy kontroluje, zda-li má daný uživatel potřebná oprávnění k provedení této akce. Tedy například, zda-li je uživatel oprávněn vytvářet nové tabulky nebo měnit či mazat stávající.

Veškeré informace o struktuře databáze a relací (entit) ukládá SŘBD do speciálních databázových tabulek v rámci systémového katalogu (db INFORMATION_SCHEMA). Tyto databázové tabulky obsahují údaje o všech objektech v databázi, jako jsou názvy relací (entit), atributů (sloupců), datové typy, primární a cizí klíče, indexy, doménová integritní omezení nebo uživatelská oprávnění.

Při vytváření nové relace SŘBD provede vložení záznamu o nové relaci do db tabulky TABLES a dále o každém jejím atributu (sloupci) do db tabulky COLUMNS. Při vytvoření primárního klíče i cizího klíče se pak zapíše nové entitní, resp. referenční integritní omezení do db tabulky TABLE_CONSTRAINTS a KEY_COLUMN_USAGE. Při vytváření nového pravidla doménové integrita se zapíše nový záznam do db tabulky CHECK_CONSTRAINTS.

Se všemi těmito zapsanými informacemi bude SŘBD dále pracovat během příkazů z rodiny DML či DQL.