title: Add a column with a reference from composite primary key to hierachical table
body: <p>I'm trying to create a hierarchical table with a composite primary key. However I also want a ParentNode to be set following the <a href="https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-2017#BKMK_EnforcingTrees" rel="nofollow noreferrer">official documentation</a>. However since I have a composite PK from UserID and NodeID, I can't get this to work without explicitly adding the constraint afterwards.</p>
  
  <p>This is the relevant query:</p>
  
  <pre><code>CREATE TABLE [dbo].[OrderStatus](
      [NodeID] HIERARCHYID NOT NULL,
      [StatusID] INT UNIQUE IDENTITY(1, 1) NOT NULL, 
      [Level] AS [NodeID].GetLevel(),
      [ParentID] AS [NodeID].GetAncestor(1) PERSISTED REFERENCES [dbo].[OrderStatus](UserID, NodeID),
      [Reason] NVARCHAR(MAX) NOT NULL,
      [Info] NVARCHAR(MAX),
      [SignatureRequired] BIT NOT NULL DEFAULT 0,
      [BarcodeRequired] BIT NOT NULL DEFAULT 0,
      [PhotoRequired] BIT NOT NULL DEFAULT 0,
      [UserID] INT NOT NULL,
      PRIMARY KEY (UserID, NodeID)
  )
  GO
  
  CREATE INDEX [OrderStatus_BreadthFirst] ON [OrderStatus](Level, NodeID)
  GO
  
  INSERT INTO [dbo].[OrderStatus] (NodeID, Reason, UserID)
  VALUES
  ('/', 'Root', 1),
  ('/0/', 'Fulfilled', 1),
  ('/1/', 'Partially fulfilled', 1),
  ('/2/', 'Not fulfilled', 1)
  </code></pre>
  
  <p>Now basically I have two questions as to what would be possible:</p>
  
  <ol>
  <li>Would setting <code>[ParentID] AS [NodeID].GetAncestor(1) PERSISTED REFERENCES [dbo].[OrderStatus](UserID, NodeID)</code> somehow be possible?
  And if it isn't,</li>
  <li>How can I work around <code>GetAncestor(1)</code> with a composite PK?</li>
  </ol>
  
  <p>Thanks in advance for your answers!</p>