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


-
Published Monday, March 9, 2009 8:46 PM by simonsabin

Comments

No Comments