Enforcing parent child relationship with Path Hierarchy model
With the classic adjacency model its dead easy to
validate that a parent exists, its a bit more difficult with path model.
The adjacency model you have a table with a self join you can easily add a
foreign key between the two columns.
When you store a path the difficulty is that you don't have anything that
looks like the parent node, and so you have to create a column that
does.
case
when charindex('\',NodePath) =
len(NodePath) then null
else
left(NodePath,len(NodePath)-charindex('\',reverse(NodePath),2)+1)
end
This set of code gets the parent node.
To use it in a foreign key you need a couple of things.
1. Firstly you need to use it as a computed column
2. You need to make sure its the same size as the parent column
3. Then you need to persist it
4. Create the forign key
This creates the table and the foreign key
create table
Org (
NodePath varchar(200)
not null primary key
, ParentPath
as
cast(case when
charindex('\',NodePath) = len(NodePath) then
null
else
left(NodePath,len(NodePath)-charindex('\',reverse(NodePath),2)+1)
end
as varchar(200))
persisted)
go
alter table
Org add constraint fk_Org_ParentPath
foreign key (ParentPath) references Org(NodePath)
And this shows the process working, the last insert will fail.
insert
into Org values
('10\')
insert
into Org values
('10\9\')
insert
into Org values
('11\')
insert
into Org values
('11\9\9\')
Not only does this verify the parent but it also ensures the whole
hierarchy is valid. Some might say one of the benefits of the path model is that
you don't have this verification, well this nicely enforces it if you
do
-