Někdy potřebujeme v SQL vytvořit tabulku, která obsahuje hierarchii. Hierarchie je struktura (někdy se označuje jako parent-child hierarchie), která obsahuje závislosti mezi jednotlivými členy. Můžeme si to představit jako organizaci ve firmě. Firma má zaměstnance a ti mají svého nadřízeného. Nadřízení zaměstnanci mají opět svého nadřízeného a takto můžeme pokračovat až k CEO firmy, který už žádného nadřízeného nemá.
Hierarchie – Jak vytvořit správně parent-child strukturu v SQL tabulce?
Představme si tabulku se zaměstnanci. Hierarchie se vytváří v rámci 1 tabulky formou tzv. self-reference. To znamená, každého zaměstnance máme definovaného primárním klíčem (id_zamestnanec) a v dalším sloupci (id_nadrizeny) budeme mít cizí klíč, který povede na primární klíč id_zamestnanec.
Příklad: Vytvoříme tabulku pro firmu, která má celkem 15 zaměstnanců z toho jsou 4 manažeři a 1 ředitel firmy.
Nachystáme si pro hierarchii tabulku “zamestnanci”
Mezi jednotlivými zaměstnanci definujeme vztah nadřízenosti a podřízenosti pomocí pole id_nadrizeny:
CREATE TABLE Temp.dbo.zamestnanci ( id_zamestnanec INT PRIMARY KEY, id_nadrizeny INT, pozice VARCHAR(255), jmenoprijmeni VARCHAR(255), plat MONEY)
INSERT INTO Temp.dbo.zamestnanci ( id_zamestnanec, id_nadrizeny, pozice, jmenoprijmeni, plat)VALUES (1,11,'zamestnanec A','Jan Novák',20000), (2,11,'zamestnanec B', 'Petra Černá',23000), (3,11,'zamestnanec C', 'Petr Holomek',21000), (4,12,'zamestnanec D', 'František Netáhlo',25000), (5,12,'zamestnanec E', 'Vlastimil Táhlo',19000), (6,12,'zamestnanec F', 'Aleš Nedělám',27000), (7,13,'zamestnanec G', 'Zbyšek Pohoda',24000), (8,13,'zamestnanec H', 'Daniela Zuřivá',22000), (9,14,'zamestnanec I', 'Lenka Malá',21500), (10,14,'zamestnanec J', 'Pavel Držgrešle',29000), (11,15,'manager 1', 'Milan Flákač',35000), (12,15,'manager 2', 'Martin Nedoma',40000), (13,15,'manager 3', 'Robert Robertek',31000), (14,15,'manager 4', 'Miloš Novák',50000), (15,NULL,'ředitel firmy', 'Jan Přísný',90000)
Přidáme cizí klíč
Přidáme Selfreferenci na pole id_nadrizeny:
ALTER TABLE [dbo].[zamestnanci] WITH CHECK ADD CONSTRAINT [FK_zamestnanci_zamestnanci] FOREIGN KEY([id_nadrizeny])REFERENCES [dbo].[zamestnanci] ([id_zamestnanec])
Výsledek testovací tabulky a selfreference
Ve výsledku máme takovou hezkou tabulku obsahující cizí klíč sama do sebe (selfreferenci):
V některém z dalších článků připomenu, jak se na parent-child strukturu dotazovat skripty pomocí rekurzivních dotazů a jak se vypořádat s hierarchiemi při reportování přes Reporting Services a Power BI.
