Self Referencing Entity Check Constraint

II have a self referencing entity with the following fields:

ID - Primary Key (also a foreign key in another table) ParentID
Name

This creates a parent/ctree which can have many different levels.

The ParentID is a reference to the parent of the object if there is one.

What i need to do is to make a check constraint that can check when something is added in another table referencing this object that the object being referenced is at the bottom of the parent child tree. ie, it has no children.

It is easy enough to check if something has a parent but as there is no reference to a child is there an easy way to do this?

Any help would be appreciated. Received on Fri May 23 2003 - 17:12:02 CEST