title: How to avoid circular relationship in SQL-Server?
body: <p>I am creating a self-related table:</p>
  
  <p>Table <code>Item</code> columns:<br>
  ItemId int - PK;<br>
  Amount money - not null;
  Price money - a computed column using a UDF that retrieves value according to the items ancestors' Amount.<br>
  ParentItemId int - nullable, reference to another ItemId in this table.  </p>
  
  <p>I need to avoid a loop, meaning, a sibling cannot become an ancestor of his ancestors, meaning, if ItemId=2 ParentItemId = 1, then ItemId 1 ParentItemId = 2 shouldn't be allowed.</p>
  
  <p>I don't know what should be the best practice in this situation.
  I think I should add a CK that gets a Scalar value from a UDF or whatever else.</p>
  
  <p><strong>EDIT:</strong>
  Another option is to create an INSTEAD OF trigger and put in 1 transaction the update of the ParentItemId field and selecting the Price field from the @@RowIdentity, if it fails cancel transaction, but I would prefer a UDF validating.</p>
  
  <p>Any ideas are sincerely welcomed.</p>