0

I am very new to HierarchyId world and trying to implement HierarchyId() into my SQLServer design and I was under impression that SQL Server allows only one Root node per Column per table and if I try to something like following if a table already has a Root node then it should error out or throw some warning:

INSERT INTO dbo.TeamHierarchy
                ( TeamNode, TeamId, TeamNodeName )
        VALUES  ( HierarchyId::GetRoot(), -- TeamNode - hierarchyid
                  1, -- TeamId - int
                  'TeamContainer'  -- TeamNodeName - varchar(50)
                  )

My assumption was based on some of the threads that I read :

SQL 2008 HierarchyID with Multiple Root Nodes

However, it seems like SQL server does not have any problem in having multiple roots because the insert query above works perfectly fine without any errors/warning. Has anything changed since SQL server 2008 since I am using SQL Server 2012? Or there was any misunderstanding on my side?

Community
  • 1
  • 1
Lost
  • 12,007
  • 32
  • 121
  • 193

1 Answers1

1

Like any other non-primary key column, you can have multiple values in a column if the value is not constrained. HierarchyID is no exception. You can have multiple roots for your use case if the HierarchyID is not a primary key. However if you want to constrain the Node, you need to make the HierarchyID a Primary Key, then you can only have 1 root, with many nodes under the root.

Microsoft discusses how to enforce uniqueness here:
https://technet.microsoft.com/en-us/library/Bb677212(v=SQL.105).aspx

UselessSQL
  • 46
  • 2