Moderný reporting pracujeme s MySQL (prepájame tabuľky pomocou referencií)

 

Máme pripravené tabuľky spolu s datami. Čaká nás však veľmi dôležitá udalosť – tieto tabuľky musíme prepojiť tak, aby sme zabezpečitli tzv. referenčnú integritu. Inak povedané, aby niekde v systéme nenastala situácia, že užívateľ priradí zákazke zľavu číslo 6, ak ich reálne má k dispozícii iba 5. Poprípade, ak by sa tak stalo musel by zľavu riadne nadefinovať, resp. nechať si ju schváliť vedením.

Veľmi pekne je táto vlastnosť popísaná v nasledujúcom odkaze od W3schools. Pokúsim sa ju načrtnúť na našom príklade:

Pri query typu: select*from sales.sales limit 5; nám program vráti nasledujúcu tabuľku:

Na prvý pohľad môžeme konštatovať, že stĺpce ako Naklady, Predajca, Mesto apod. nemajú zmysel bez toho aby sme vedeli, že pod predajcom 1 sa ukrýva Peter atď. 

V tomto prípade môžeme povedať, že: Tabuľka Sales v stĺpci Predajca neobsahuje primárne hodnoty, ale tzv. foreign_key hodnoty, ktoré sa rovnajú ID hodnotám v tabuľke Dealer.

Všeobecne sa jedná o nadefinovanie Primary a Foreign Key medzi tabuľkami. Primary key som už spomínal pri tvorbe tabuliek. Prejdime na príkaz Foreign Key:

Pri tvorbe tabuliek ho môžeme zakomponovať takto:

CREATE TABLE Tabulka
(Tab_ID int NOT NULL, Tab_Stlpec Varchar(50) NOT NULL,
Zakodovana_Premenna int NOT NULL,
PRIMARY KEY (Tab_Id), FOREIGN KEY (Zakodovana_Premenna) REFERENCES Dealer(ID));

Tento príkaz je skoro totožný s klasickým CREATE TABLE, avšak na konci sme okrem Primary Key definovali Foreign key (Stĺpec, kde namiesto textov bude odkaz na ID v referenčnej tabuľke) a referenciu (tabuľka Dealer, resp. jej stĺpec ID).

Obecne platí, že hodnoty vo foreign key v istej tabuľke sú rovné primary key hodnotám v referenčnej tabuľke.

Čo je ďalej potrebné dodať? Okrem definovania aj správanie sa pri uploadovaní a vymazávaní záznamov:

Jedná sa o možnosti ON UPDATE a ON DELETE. Pre lepšiu manipuláciu s tabuľkami však nebudeme používať možnosť FOREIGN KEY ale CONSTRAINT – dôvod? – lepšia manipulácia pri mazaní / zmenách referencií.

CREATE TABLE Tabulka
(Tab_ID int NOT NULL, Tab_Stlpec Varchar(50) NOT NULL,
Zakodovana_Premenna int NOT NULL,
PRIMARY KEY (Tab_Id), CONSTRAINT Prepojenie1
    FOREIGN KEY (Zakodovana_Premenna ) REFERENCES Dealer (ID) ON DELETE CASCADE ON UPDATE CASCADE);

Malá zmena kódu, kde sme použili pre obe možnosti úpravy CASCADE. ON DELETE znamená, udalosť, keď sa zmaže záznam v referenčnej tabuľke (zmažem konkrétneho dealera, na ktorého sa odkazuje niektorá z iných tabuliek). ON UPDATE čo sa stane s odkazujúcimi sa tabuľkami pri zmene záznamu v referenčnej tabuľke (Zmením Priezvisko dealera, ktorý bol už použitý v iných tabuľkách – teda niečo predal alebo dostal výplatu).

Tieto udalosti môžeme definovať nasledujúcimi možnosťami:

Set NULL : Ak zmažem záznam, v ostatných tabuľkách sa namiesto čísel vo foreign key objavia hodnoty null.

CASCADE : Aplikuje zmenu v odkazujúcich sa tabuľkách.

RESTRICT : Zabráni vymazaniu referenčnej hodnoty v prípade, že sa používa v iných tabuľkách.

NO ACTION :V podstate podobná ako RESTRICT, akurát systém nerobí kontrolu pred jeho exekúciou (nebudeme používať, teda ani zaoberať sa).

Obvykle sa používa nasledujúca kombinácia:

ON DELETE RESTRICT – zabráni zmazaniu používaných referencií

ON UPDATE CASCADE – použije zmenu referenčnej hodnoty v odkazujúcich sa tabuľkách

Ak by sme pripravovali tabuľku Cost odznova vrátane referencií, použijeme nasledujúci kód:

CREATE TABLE cost (ID int(11) NOT NULL AUTO_INCREMENT, Predajca int(11) NOT NULL,
   Mesiac int(11) NOT NULL, Rok int(11) NOT NULL, Mzda decimal(10,2) NOT NULL,
   Sprava decimal(10,2) NOT NULL, Bonus decimal(10,2) DEFAULT NULL, PRIMARY KEY (ID),
   KEY Dealer_Typ (Predajca), CONSTRAINT Dealer_Typ FOREIGN KEY (Predajca) REFERENCES dealer (ID) ON DELETE RESTRICT ON UPDATE CASCADE);

Nás však zaujíma úprava už vytvorených tabuliek. Takže budeme pridávať CONSTRAINTS pre tabuľky sales a cost:

-- Foreign Keys - Sales
ALTER TABLE sales add
   CONSTRAINT Auto_Typ FOREIGN KEY (Auto) REFERENCES car (ID)ON DELETE RESTRICT ON UPDATE CASCADE,
Diskont_Typ FOREIGN KEY (Diskont) REFERENCES discount (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
   add CONSTRAINT Mesto_Typ FOREIGN KEY (Mesto) REFERENCES city (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
   add CONSTRAINT Naklady_Typ FOREIGN KEY (Naklady) REFERENCES cost (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
   add CONSTRAINT Predajca_Typ FOREIGN KEY (Predajca) REFERENCES dealer (ID) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Foreign Keys - Cost
ALTER TABLE cost ADD CONSTRAINT Dealer_Typ FOREIGN KEY (Predajca) REFERENCES dealer (ID) ON DELETE RESTRICT ON UPDATE CASCADE;

Pre ilustráciu ak datáte príkaz show create table sales.sales; SQL Vám vráti celý proces tvorby existujúcej tabuľky:

CREATE TABLE `sales` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `Naklady` int(11) NOT NULL,   `Predajca` int(11) NOT NULL,   `Mesto` int(11) NOT NULL,   `Auto` int(11) NOT NULL,   `Diskont` int(11) NOT NULL,   `Predaj` int(11) DEFAULT NULL,   `Mesiac` int(11) NOT NULL,   `Rok` int(11) NOT NULL,   PRIMARY KEY (`ID`),   KEY `Naklady_Typ` (`Naklady`),   KEY `Mesto_Typ` (`Mesto`),   KEY `Auto_Typ` (`Auto`),   KEY `Diskont_Typ` (`Diskont`),   KEY `Predajca_Typ` (`Predajca`),   CONSTRAINT `Auto_Typ` FOREIGN KEY (`Auto`) REFERENCES `car` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,   CONSTRAINT `Diskont_Typ` FOREIGN KEY (`Diskont`) REFERENCES `discount` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,   CONSTRAINT `Mesto_Typ` FOREIGN KEY (`Mesto`) REFERENCES `city` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,   CONSTRAINT `Naklady_Typ` FOREIGN KEY (`Naklady`) REFERENCES `cost` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,   CONSTRAINT `Predajca_Typ` FOREIGN KEY (`Predajca`) REFERENCES `dealer` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=17473 DEFAULT CHARSET=utf8

Všimnite si, že mám neošetrenú udalosť ON DELETE. Ak by som potreboval jednotlivé kľúče zmeniť, jednoducho ich vymažem pomocou  nasledujúceho kódu (nezabudnite vypnúť kontrolu kľúčov (neskôr opäť zapnúť) - SET FOREIGN_KEY_CHECKS=0; potom to isté ale s 1)´:

alter table sales.sales drop foreign key Predajca_Typ;

Zistíme, že tabuľka Sales už neobsahuje CONSTRAINT Predajca_Typ. Tým pádom nám stačí si upraviť tabuľku tak, že jednoducho pridáme opäť ten istý kľúč, ale už so správnou definíciou ON DELETE:

ALTER TABLE sales
   add CONSTRAINT Predajca__Typ FOREIGN KEY (Predajca) REFERENCES dealer (ID) ON DELETE RESTRICT ON UPDATE CASCADE;

A opäť sa pozrieme na SHOW CREATE TABLE, zistíme že zmena bola prevedená. POZNÁMKA: Tieto zmeny sa snažte robiť v command line – nie vo Workbench, aby ste predišli komplikáciám.

ERR diagram vo finálnej fáze (po refreshnutí) vyzerá nasledovne:

Tým pádom môžem konštatovať, že sme si pripravili databázku, s ktorou môžeme pracovať. Tí, ktorí ste mali komplikácie – pokúste sa ju stiahnuť a vložiť folder do zložky C:\MySQL- Data\data