Re: Self Referencing Entity Check Constraint

valentine_rob_at_hotmail.com (Rob V) wrote in message news:<47d5fd53.0305230712.3e00571e_at_posting.google.com>...
> 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.

Well, It is not difficult to find all the rows that do not have descendants.
You simply do something like,
  select id from tab_name t1 where not exists (select 1 from

         tab_name t2 where t1.id=t2.parent_id); The problem is with the check constraint. You can not use queries with a check constraint. You can not use a user defined function with a check constraint. This will need to be implemented with a trigger on the table that references this table. In the trigger you check if the rows being modified reference any childless rows, or any other conditions you wish, in this table.

• Jusung Yang