dotNETportal.cz

Pokud chceme v MS SQL Serveru ukládat nějakou hierarchickou strukturu, můžeme s výhodou použít datový typ hierarchyid (v MS SQL Serveru je od verze 2008).

Pokud je ale touto hierarchickou strukturou strom, samotná definice sloupce typu hierarchyid nám nezajistí udržování potřebné datové integrity tj. automaticky nám nezajistí, že libovolná ukládaná data budou vždy opravdu tvořit validní strom. Konkrétně se jedna o zajištění těchto podmínek:

• Uložené hodnoty sloupce typu hierarchyid musí být jedinečné.
• Pokud v tabulce existuje záznam s nějakou hodnotou sloupce typu hierarchyid, musí v této tabulce existovat záznam s hodnotou odpovídající přímému předkovi.
• Záznam odpovídající přímému předkovi nějakého jiného záznamu nejde odstranit dokud tyto jiné záznamy v tabulce existují.

(Žádná z těchto podmínek není u sloupce typu hierarchyid automaticky vynucená - více naleznete v části Limitations of hierarchyid v dokumentaci na MSDN.)

Co tedy musíme udělat, aby jsme splnění těchto podmínek vynutili?

Ukážeme si to rovnou na příkladu. Nejprve si vytvořme tabulku Node pro reprezentaci stromové struktury:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.Node(
	NodeID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(100) NOT NULL,
	NodeHID hierarchyid NOT NULL,
 CONSTRAINT PK_Node PRIMARY KEY CLUSTERED 
(
	NodeID ASC
))
GO

Zajištění první podmínky na jedinečnost HID je jednoduché – stačí nad sloupcem NodeHID doplnit UNIQUE constraint:

ALTER TABLE dbo.Node ADD CONSTRAINT
	U_Node_NodeHID UNIQUE NONCLUSTERED 
	(
	NodeHID
	)
GO

Zajištění druhé a třetí podmínky je o něco málo složitější. Potřebujeme udělat následující:

1. Do tabulky Node doplnit odvozený sloupec, který bude vracet HID předka.

Pokud navíc nebudeme chtít do tabulky ukládat pouze jediný kořenový prvek - záznam s HID ‘’ (výsledek volání hierarchyid::GetRoot()), ale budeme chtít jako první úroveň ukládat rovnou několik “kořenových” prvků s HID ‘/1/’, ‘/2/’ apod., musíme ve výrazu odvozeného sloupce ParentNodeHID pro tyto prvky první úrovně vracet hodnotu NULL. Výraz pro odvozený sloupec pak bude:

CASE WHEN NodeHID.GetLevel()=1 THEN NULL ELSE NodeHID.GetAncestor(1) END

1. Tento odvozený sloupec musí být vytvořen s klauzulí PERSISTED.
2. Do tabulky Node doplníme FOREIGN KEY constraint mezi sloupci NodeHID a ParentNodeHID.

ALTER TABLE dbo.Node ADD
	ParentNodeHID  AS CASE WHEN NodeHID.GetLevel() = 1 THEN NULL ELSE NodeHID.GetAncestor(1) END PERSISTED 
GO
ALTER TABLE dbo.Node WITH CHECK ADD CONSTRAINT FK_Node_Node FOREIGN KEY(ParentNodeHID)
	REFERENCES dbo.Node (NodeHID)
GO

Nyní nám již SQL Server nedovolí ukládat hodnoty hierarchyid, pro které nemáme uložené nadřazené prvky (s výjimkou první úrovně), ani vnitřní uzly stromu odstranit.

Pokud by jsme dále chtěli, aby pod jedním uzlem neexistovali dva prvky se stejným názvem (sloupec Name), můžeme k tomu také využít odvozený sloupec ParentNodeHID a doplnit druhý UNIQUE constraint:

ALTER TABLE dbo.Node ADD CONSTRAINT
	U_Node_ParentNodeHID_Name UNIQUE NONCLUSTERED 
	(
	ParentNodeHID,
	Name
	)
GO

Výsledný SQL skript pro vytvoření tabulky Node včetně všech omezení vypadá takto:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.Node(
	NodeID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(100) NOT NULL,
	NodeHID hierarchyid NOT NULL,
	ParentNodeHID  AS CASE WHEN NodeHID.GetLevel()=1 THEN NULL ELSE NodeHID.GetAncestor(1) END PERSISTED,
 CONSTRAINT PK_Node PRIMARY KEY CLUSTERED 
(
	NodeID ASC
))
GO
ALTER TABLE dbo.Node ADD CONSTRAINT
	U_Node_NodeHID UNIQUE NONCLUSTERED 
	(
	NodeHID
	)
GO
ALTER TABLE dbo.Node WITH CHECK ADD CONSTRAINT FK_Node_Node FOREIGN KEY(ParentNodeHID)
	REFERENCES dbo.Node (NodeHID)
GO
ALTER TABLE dbo.Node ADD CONSTRAINT
	U_Node_ParentNodeHID_Name UNIQUE NONCLUSTERED 
	(
	ParentNodeHID,
	Name
	)
GO