Re: Validating a hierarchical query

In article <455f7154.0409300752.5d3deeab_at_posting.google.com>, Phil Bewig wrote:
> I can write a hierarchical query against the
> employees sample table like this:
>
> select lpad(' ', (level-1)*2, ' ') ||
> first_name || ' ' || last_name
> from employees
> start with manager_id is null
> connect by prior employee_id = manager_id
> order siblings by last_name
>
> For purposes of writing a validation suite,
> I would like to check that the hierarchy is
> valid, forming a singly-rooted directed
> acyclic graph. It is easy enough to test
> that only one record has a null manager_id,
> that no record has equal employee_id and
> manager_id, and that each manager_id appears
> in the employee_id field of some record in
> the table. But testing that there are no
> cycles, and no breaks in the hierarchical
> chain, is harder. Can anyone suggest how to
> make the needed tests?
>
> Many thanks,
>
> Phil

Phil,

There are no cycles if employee_id is a primary key and manager_id is a foreign key referencing employee_id.

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/