I have a table similar to the following that holds hierarchies from difference sources (SourceId).

CREATE TABLE dbo.Hierarchies
    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

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 GetAncestor?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.