2

Hierarchy data type in sql server has duplicate

I have the following table

ChildID ParentID
1   0
1   2
4   3
1   4
2   6
4   8

as base for the child to parent relationship

Running query:

CREATE TABLE #Org (
OrgNode [hierarchyid],  
ChildID int,
ParentID int  
CONSTRAINT PK_OrgNode PRIMARY KEY CLUSTERED (OrgNode));  

CREATE TABLE #Child (
ChildID int, 
ParentID int,
Num int);  
CREATE CLUSTERED INDEX tmpind ON #Child(ParentID, ChildID); 

INSERT INTO #Child 
SELECT  DISTINCT
ChildID = ChildID,
ParentID = ParentID, 
Num = ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY ParentID)
FROM #tmpEntity;

The result is as expected:

ChildID ParentID    Num
1   0   1
1   2   1
4   3   1
1   4   1
2   6   1
4   8   1

when running the next step

WITH paths(path, ChildID)   
AS 
(SELECT hierarchyid::GetRoot() AS OrgNode, ChildID   
 FROM #Child AS C   
 WHERE ParentID = 0   
 UNION ALL
 SELECT CAST(p.path.ToString() + CAST(C.Num AS varchar(30)) + '/' AS hierarchyid), C.ChildID  
 FROM #Child AS C   
 JOIN paths AS p ON C.ParentID = P.ChildID)  
INSERT #Org (OrgNode, O.ChildID, O.ParentID)  
SELECT P.path, O.ChildID, O.ParentID  
FROM #tmpEntity AS O   
JOIN Paths AS P ON O.ChildID = P.ChildID
OPTION (maxrecursion 0)
GO

it throws an error now

"Msg 2627, Level 14, State 1, Line 170 Violation of PRIMARY KEY constraint 'PK_OrgNode'. Cannot insert duplicate key in object 'dbo.#Org'. The duplicate key value is (0x). The statement has been terminated."

Can anyone help or see where the bug is? Thanks

Dom
  • 21
  • 3
  • 1
    Hi and welcome to SO. Nice job attempting to post ddl and sample data. However, the table names don't match and there is no data for #Org. If that data is the first bit of sample data it is missing the hierarchyid. And don't be scared to use tabs and white space in your code so you can read it. – Sean Lange May 16 '19 at 13:30
  • +1 to @SeanLange's feedback. This may be a learning opportunity for me, but what is `hierarchyid::GetRoot()`? – Brian May 16 '19 at 14:59
  • 1
    @Brian it is one of the methods of the hierarchyid datatype. https://learn.microsoft.com/en-us/sql/t-sql/data-types/getroot-database-engine?view=sql-server-2017 – Sean Lange May 16 '19 at 15:01
  • @user9416131, what are the results if you comment out the `INSERT #Org (OrgNode, O.ChildID, O.ParentID)` line and just look at the results of the select? – Brian May 16 '19 at 15:18
  • @Sean Lange, the data are populated via an earlier part of the script. The data for #Org are populated through the script from the with statement. – Dom May 20 '19 at 00:45
  • @user9416131, if comment out the insert statment the result is path ChildID ParentID 0x 1 2 0x 1 4 0x 1 0 It shows the background for the error/violation of the PRIMARY KEY. But I still not see why. – Dom May 20 '19 at 00:53
  • "the data are populated via an earlier part of the script" I assumed that. But without that information to work with anybody trying to help is guessing. How can we help when we don't know the data. Only thing I can suggest is look at the select statement and see what primary key value(s) would be duplicated. – Sean Lange May 20 '19 at 13:04

1 Answers1

0

The message:

"Msg 2627, Level 14, State 1, Line 170 Violation of PRIMARY KEY constraint 'PK_OrgNode'. Cannot insert duplicate key in object 'dbo.#Org'. The duplicate key value is (0x). The statement has been terminated."

indicates that you've provided multiple root nodes to the hierarchy. The primary key constrains OrgNode to unique values. If that was the intended result, all you would need to do is remove the primary key constraint from OrgNode and enforce a tree on the application level.

see this post for info on how to do so

However, if a single root node was the desired result, then we'll need to dive into the data you're using.

The data that's being inserted in the final command looks like this:

path    ChildID ParentID
0x      1       0
0x      1       2
0x      1       4

Its likely that you flipped the Child and ParentIds in your initial table. Instead of having a single parent branching into children, the tree based on the data provided to #tmpEntity appears like this.

3   8     6
 \ /     /
  4  0  2
   \ | /
     1

If you're target tree was instead:

     0  
     | 
     1
    / \
   2   4
  /   / \  
 6   3   8

You're initial data would have to be organized as:

ParentID ChildID 
0        1
1        2
4        3
1        4
2        6
4        8
David Kalamarides
  • 56
  • 5