Re: Validating a hierarchical query

"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message news:slrncloeb9.2pg.rene.nyffenegger_at_zhnt60m34.netarchitects.com...
| 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/

actually, Rene, that's how cycles occur, for example:

SQL> select empno, ename, mgr
  2 from emp
  3 order by empno
  4 /

     EMPNO ENAME MGR
---------- ---------- ----------

      7369 SMITH 7902
...

      7902 FORD             7566
      7934 MILLER           7782

SQL> update emp
  2 set mgr = 7369
  3 where empno = 7902;

1 row updated.

SQL> select empno, ename, mgr
  2 from emp
  3 order by empno
  4 /

     EMPNO ENAME MGR
---------- ---------- ----------

      7369 SMITH 7902
...

      7902 FORD             7369
      7934 MILLER           7782

the PK/FK constraints do not prevent the cycle, they just enforce that any MGR value is a valid EMPNO value (could even reference empno in the same record)

pre-10g raises an error if there is a cycle

SQL> select empno, ename, mgr
  2 from emp
  3 connect by prior empno = mgr
  4 start with mgr = 7902
  5 /
ERROR:
ORA-01436: CONNECT BY loop in user data

however, 10G adds the NOCYCLE option and the CONNECT_BY_ISCYCLE pseudocolumn to make it easier to check for cycles

++ mcs Received on Thu Sep 30 2004 - 13:15:51 CDT