I have a table similar to the following that holds hierarchies from difference sources (
CREATE TABLE dbo.Hierarchies ( SourceID INTEGER NOT NULL, NodeHierarchyId HierarchyId NOT NULL, NodeName NVARCHAR(30) NOT NULL, NodeType NVARCHAR(30) NOT NULL, PRIMARY KEY CLUSTERED (SourceID, NodeHierarchyId) );
I would like to introduce a constraint that the
NodeName is unique for all children of a node. That is, a node's children should all have unique names between them. The name reoccurring in the hierarchy for a different node's children is not an issue.
My first attempt was
CREATE UNIQUE INDEX UniqueNodeChildrenNames ON dbo.Hierarchies ( SourceID, NodeHierarchyId.GetAncestor(1), NodeName );
SQL Server does not like this so my next step would be to store
GetAncestor as a column in the table.
ALTER TABLE dbo.Hierarchies ADD ParentNodeId AS NodeHierarchyId.GetAncestor(1) PERSISTED;
However, this effectively creates the parent-child structure that
HierarchyId is meant to replace. Hence my question below.
Is there a method to create this constraint without creating a column in the table for