I have a table that has a self-referencing FK. The danger in this is that it is entirely possible to create a circular reference if care is not exercised during INSERT and UPDATE.
For ease of discussion, lets say tblItems has columns ThisID and ParentID. ParentID may be NULL and is the FK, ThisID is the PK and identity.
When I select from this table I am looking for all records that don't have a parentID set, however, there comes a time when one or more items is the top level parent and retrieving its parent is not possible.
This returns all the possible records that need a parent:
Obviously a record cannot be its own parent and a record cannot be a child to one of its children and on down the line.
So, given the previous select, how can I ensure that the items selected are not found in the ParentID column of any selected record's parent, grandparent, etc.
I had considered setting the ParentID value to -1 if it is a top level item so that I could differentiate it from items without a parent, however, it is entirely possible that an item that has children can be made the child of another parent item.
For ease of discussion, lets say tblItems has columns ThisID and ParentID. ParentID may be NULL and is the FK, ThisID is the PK and identity.
When I select from this table I am looking for all records that don't have a parentID set, however, there comes a time when one or more items is the top level parent and retrieving its parent is not possible.
This returns all the possible records that need a parent:
VB.NET:
SELECT * FROM tblItems WHERE ParentID IS NULL
Obviously a record cannot be its own parent and a record cannot be a child to one of its children and on down the line.
So, given the previous select, how can I ensure that the items selected are not found in the ParentID column of any selected record's parent, grandparent, etc.
I had considered setting the ParentID value to -1 if it is a top level item so that I could differentiate it from items without a parent, however, it is entirely possible that an item that has children can be made the child of another parent item.