Enforcing Acyclic Hierarchies With SQL Assertions

2026-02-23

Introduction

Referential integrity constraints have been available since version 7 of the Oracle Database. While these constraints ensure that referenced rows exist, they cannot express more complex integrity rules that span multiple rows or tables. Check constraints are similarly limited, as they can only validate conditions on the columns of a single row.

The latest version of the Oracle AI Database (23.26.1) introduced SQL Assertions, enabling us to define cross-row and cross-table checks declaratively.

SQL Assertions support a wide range of use cases where data must satisfy global consistency rules. One particularly interesting example is ensuring that hierarchical structures remain free of cycles.

In this blog post, I will demonstrate how SQL Assertions can detect and prevent cycles in hierarchical data structures.

Setup

For the examples in this blog post, I used an Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0.

Connected as SYS I ran the following script to create the user DEMO with the necessary privileges.

1) DEMO user

create user demo identified by demo
   default tablespace users
   temporary tablespace temp
   quota unlimited on users;

grant create session, create table, create assertion to demo;

create user demo identified by demo
   default tablespace users
   temporary tablespace temp
   quota unlimited on users;

grant create session, create table, create assertion to demo;

Connected as DEMO I ran the following script to create the famous tables DEPT and EMP with initial data:

2) Install DEPT/EMP example

create table dept (
   deptno number(2, 0)      not null constraint dept_pk primary key,
   dname  varchar2(14 char) not null,
   loc    varchar2(13 char) not null
);

create table emp (
   empno    number(4, 0)      not null  constraint emp_pk primary key,
   ename    varchar2(10 char) not null,
   job      varchar2(9 char)  not null,
   mgr      number(4, 0)                constraint emp_mgr_fk references emp,
   hiredate date              not null,
   sal      number(7, 2)      not null,
   comm     number(7, 2),
   deptno   number(2, 0)      not null  constraint emp_deptno_fk references dept
);

create index emp_deptno_fk_i on emp(deptno);
create index emp_mgr_fk_i on emp(mgr);

insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK'),
       (20, 'RESEARCH',   'DALLAS'),
       (30, 'SALES',      'CHICAGO'),
       (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES',  'MANAGER',   7839, date '1981-04-02', 2975, null, 20),
       (7698, 'BLAKE',  'MANAGER',   7839, date '1981-05-01', 2850, null, 30),
       (7782, 'CLARK',  'MANAGER',   7839, date '1981-06-09', 2450, null, 10),
       (7788, 'SCOTT',  'ANALYST',   7566, date '1987-04-19', 3000, null, 20),
       (7902, 'FORD',   'ANALYST',   7566, date '1981-12-03', 3000, null, 20),
       (7499, 'ALLEN',  'SALESMAN',  7698, date '1981-02-20', 1600,  300, 30),
       (7521, 'WARD',   'SALESMAN',  7698, date '1981-02-22', 1250,  500, 30),
       (7654, 'MARTIN', 'SALESMAN',  7698, date '1981-09-28', 1250, 1400, 30),
       (7844, 'TURNER', 'SALESMAN',  7698, date '1981-09-08', 1500,    0, 30),
       (7900, 'JAMES',  'CLERK',     7698, date '1981-12-03',  950, null, 30),
       (7934, 'MILLER', 'CLERK',     7782, date '1982-01-23', 1300, null, 10),
       (7369, 'SMITH',  'CLERK',     7902, date '1980-12-17',  800, null, 20),
       (7839, 'KING',   'PRESIDENT', null, date '1981-11-17', 5000, null, 10),
       (7876, 'ADAMS',  'CLERK',     7788, date '1987-05-23', 1100, null, 20);
commit;

create table dept (
   deptno number(2, 0)      not null constraint dept_pk primary key,
   dname  varchar2(14 char) not null,
   loc    varchar2(13 char) not null
);

create table emp (
   empno    number(4, 0)      not null  constraint emp_pk primary key,
   ename    varchar2(10 char) not null,
   job      varchar2(9 char)  not null,
   mgr      number(4, 0)                constraint emp_mgr_fk references emp,
   hiredate date              not null,
   sal      number(7, 2)      not null,
   comm     number(7, 2),
   deptno   number(2, 0)      not null  constraint emp_deptno_fk references dept
);

create index emp_deptno_fk_i on emp(deptno);
create index emp_mgr_fk_i on emp(mgr);

insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK'),
       (20, 'RESEARCH',   'DALLAS'),
       (30, 'SALES',      'CHICAGO'),
       (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES',  'MANAGER',   7839, date '1981-04-02', 2975, null, 20),
       (7698, 'BLAKE',  'MANAGER',   7839, date '1981-05-01', 2850, null, 30),
       (7782, 'CLARK',  'MANAGER',   7839, date '1981-06-09', 2450, null, 10),
       (7788, 'SCOTT',  'ANALYST',   7566, date '1987-04-19', 3000, null, 20),
       (7902, 'FORD',   'ANALYST',   7566, date '1981-12-03', 3000, null, 20),
       (7499, 'ALLEN',  'SALESMAN',  7698, date '1981-02-20', 1600,  300, 30),
       (7521, 'WARD',   'SALESMAN',  7698, date '1981-02-22', 1250,  500, 30),
       (7654, 'MARTIN', 'SALESMAN',  7698, date '1981-09-28', 1250, 1400, 30),
       (7844, 'TURNER', 'SALESMAN',  7698, date '1981-09-08', 1500,    0, 30),
       (7900, 'JAMES',  'CLERK',     7698, date '1981-12-03',  950, null, 30),
       (7934, 'MILLER', 'CLERK',     7782, date '1982-01-23', 1300, null, 10),
       (7369, 'SMITH',  'CLERK',     7902, date '1980-12-17',  800, null, 20),
       (7839, 'KING',   'PRESIDENT', null, date '1981-11-17', 5000, null, 10),
       (7876, 'ADAMS',  'CLERK',     7788, date '1987-05-23', 1100, null, 20);
commit;

Hierarchical SQL Query

Let’s look at the acyclic data first.

3) Hierarchical Query

column ename format a15
 select lpad(' ', 2 * (level - 1)) || ename as ename,
        empno,
        mgr
   from emp
connect by prior empno = mgr
  start with ename = 'KING';

column ename format a15
 select lpad(' ', 2 * (level - 1)) || ename as ename,
        empno,
        mgr
   from emp
connect by prior empno = mgr
  start with ename = 'KING';

ENAME                EMPNO        MGR
--------------- ---------- ----------
KING                  7839           
  JONES               7566       7839
    SCOTT             7788       7566
      ADAMS           7876       7788
    FORD              7902       7566
      SMITH           7369       7902
  BLAKE               7698       7839
    ALLEN             7499       7698
    WARD              7521       7698
    MARTIN            7654       7698
    TURNER            7844       7698
    JAMES             7900       7698
  CLARK               7782       7839
    MILLER            7934       7782

14 rows selected.

ENAME                EMPNO        MGR
--------------- ---------- ----------
KING                  7839           
  JONES               7566       7839
    SCOTT             7788       7566
      ADAMS           7876       7788
    FORD              7902       7566
      SMITH           7369       7902
  BLAKE               7698       7839
    ALLEN             7499       7698
    WARD              7521       7698
    MARTIN            7654       7698
    TURNER            7844       7698
    JAMES             7900       7698
  CLARK               7782       7839
    MILLER            7934       7782

14 rows selected.

Detecting Cycles Via SQL Query

We see that KING is the only employee without a manager. With the next update, we create a cycle by making SCOTT the manager of KING:

4) Create Cycle

update emp 
   set mgr = 7788 
 where empno = 7839;
commit;

update emp 
   set mgr = 7788 
 where empno = 7839;
commit;

When we now re-run the previous query, we get an ORA-01436: CONNECT BY loop in user data error.

Of course, we can work around it by adding a NOCYCLE parameter to produce a result:

5) NOCYCLE Hierarchical Query

 select lpad(' ', 2 * (level - 1)) || ename as ename,
        empno,
        mgr
   from emp
connect by nocycle prior empno = mgr
  start with ename = 'KING';

 select lpad(' ', 2 * (level - 1)) || ename as ename,
        empno,
        mgr
   from emp
connect by nocycle prior empno = mgr
  start with ename = 'KING';

ENAME                EMPNO        MGR
--------------- ---------- ----------
KING                  7839       7788
  JONES               7566       7839
    SCOTT             7788       7566
      ADAMS           7876       7788
    FORD              7902       7566
      SMITH           7369       7902
  BLAKE               7698       7839
    ALLEN             7499       7698
    WARD              7521       7698
    MARTIN            7654       7698
    TURNER            7844       7698
    JAMES             7900       7698
  CLARK               7782       7839
    MILLER            7934       7782

14 rows selected.

ENAME                EMPNO        MGR
--------------- ---------- ----------
KING                  7839       7788
  JONES               7566       7839
    SCOTT             7788       7566
      ADAMS           7876       7788
    FORD              7902       7566
      SMITH           7369       7902
  BLAKE               7698       7839
    ALLEN             7499       7698
    WARD              7521       7698
    MARTIN            7654       7698
    TURNER            7844       7698
    JAMES             7900       7698
  CLARK               7782       7839
    MILLER            7934       7782

14 rows selected.

However, I want a query that shows the reason for the cycle, rather than ignoring it. Something like this:

6) Detecting Cycles

column ename_path format a20
select *
  from (
          select e.ename,
                 e.empno,
                 e.mgr,
                 connect_by_iscycle as is_cycle,
                 sys_connect_by_path(e.ename, '/') as ename_path
            from emp e
         connect by nocycle prior e.empno = e.mgr
           start with e.mgr is not null
       )
 where is_cycle = 1;

column ename_path format a20
select *
  from (
          select e.ename,
                 e.empno,
                 e.mgr,
                 connect_by_iscycle as is_cycle,
                 sys_connect_by_path(e.ename, '/') as ename_path
            from emp e
         connect by nocycle prior e.empno = e.mgr
           start with e.mgr is not null
       )
 where is_cycle = 1;

ENAME                EMPNO        MGR   IS_CYCLE ENAME_PATH          
--------------- ---------- ---------- ---------- --------------------
JONES                 7566       7839          1 /SCOTT/KING/JONES   
SCOTT                 7788       7566          1 /KING/JONES/SCOTT   
KING                  7839       7788          1 /JONES/SCOTT/KING

ENAME                EMPNO        MGR   IS_CYCLE ENAME_PATH          
--------------- ---------- ---------- ---------- --------------------
JONES                 7566       7839          1 /SCOTT/KING/JONES   
SCOTT                 7788       7566          1 /KING/JONES/SCOTT   
KING                  7839       7788          1 /JONES/SCOTT/KING

SQL Assertion Using Connect By

According to the CREATE ASSERTION syntax diagram, we should be able to use the previous query within the SQL Assertion as follows:

7) Create assertion with CONNECT_BY

create assertion if not exists emp_mgr_fk_no_cycle_as
   check (
      not exists (
         select 'cycle detected'
           from (
                   select connect_by_iscycle as is_cycle
                     from emp e
                  connect by nocycle prior e.empno = e.mgr
                    start with e.mgr is not null
                )
          where is_cycle = 1
      )
   );

create assertion if not exists emp_mgr_fk_no_cycle_as
   check (
      not exists (
         select 'cycle detected'
           from (
                   select connect_by_iscycle as is_cycle
                     from emp e
                  connect by nocycle prior e.empno = e.mgr
                    start with e.mgr is not null
                )
          where is_cycle = 1
      )
   );

ORA-08689: CREATE ASSERTION failed
ORA-08677: Certain SQL functions are not supported.

ORA-08689: CREATE ASSERTION failed
ORA-08677: Certain SQL functions are not supported.

Unfortunately, this does not work. We get a generic error. The documentation for ORA-08677: Certain SQL functions are not supported. does not reveal the culprit either. However, there is a rather long list of limitations listed at the end of this section for the CREATE ASSERTION statement in the SQL Language Reference.

After some experimentation, I have concluded that as of version 23.26.1, hierarchical queries are not supported in SQL assertions and that this limitation is not explicitly documented.

SQL Assertion Using Joins

It would be useful to be able to use hierarchical queries to identify cycles within hierarchies.

However, we can tighten up the business requirements. From

“Cycles are not allowed for the emp_mgr_fk relationship.”

“The number of levels in the emp_mgr_fk relationship is limited to 5.”

Now we can rewrite the CREATE ASSERTION statement like this:

8) Create assertion with joins

create assertion if not exists emp_mgr_fk_no_cycle_as
   check (
      not exists (
         select '6 Levels detected, cycle assumed'
           from emp e1, emp e2, emp e3, emp e4, emp e5, emp e6
          where e1.mgr is not null and e1.mgr = e2.empno
            and e2.mgr is not null and e2.mgr = e3.empno
            and e3.mgr is not null and e3.mgr = e4.empno
            and e4.mgr is not null and e4.mgr = e5.empno
            and e5.mgr is not null and e5.mgr = e6.empno
      )
   );

create assertion if not exists emp_mgr_fk_no_cycle_as
   check (
      not exists (
         select '6 Levels detected, cycle assumed'
           from emp e1, emp e2, emp e3, emp e4, emp e5, emp e6
          where e1.mgr is not null and e1.mgr = e2.empno
            and e2.mgr is not null and e2.mgr = e3.empno
            and e3.mgr is not null and e3.mgr = e4.empno
            and e4.mgr is not null and e4.mgr = e5.empno
            and e5.mgr is not null and e5.mgr = e6.empno
      )
   );

ORA-08689: CREATE ASSERTION failed
ORA-08601: SQL assertion (DEMO.EMP_MGR_FK_NO_CYCLE_AS) violated.

ORA-08689: CREATE ASSERTION failed
ORA-08601: SQL assertion (DEMO.EMP_MGR_FK_NO_CYCLE_AS) violated.

This error is expected since we have a cycle in our data. Let’s fix that.

9) Remove Cycle

update emp 
   set mgr = null 
 where ename = 'KING';
commit;

update emp 
   set mgr = null 
 where ename = 'KING';
commit;

Let’s try to create the SQL Assertion again.

10) Create assertion with joins (try again)

create assertion if not exists emp_mgr_fk_no_cycle_as
   check (
      not exists (
         select '6 Levels detected, cycle assumed'
           from emp e1, emp e2, emp e3, emp e4, emp e5, emp e6
          where e1.mgr is not null and e1.mgr = e2.empno
            and e2.mgr is not null and e2.mgr = e3.empno
            and e3.mgr is not null and e3.mgr = e4.empno
            and e4.mgr is not null and e4.mgr = e5.empno
            and e5.mgr is not null and e5.mgr = e6.empno
      )
   );

create assertion if not exists emp_mgr_fk_no_cycle_as
   check (
      not exists (
         select '6 Levels detected, cycle assumed'
           from emp e1, emp e2, emp e3, emp e4, emp e5, emp e6
          where e1.mgr is not null and e1.mgr = e2.empno
            and e2.mgr is not null and e2.mgr = e3.empno
            and e3.mgr is not null and e3.mgr = e4.empno
            and e4.mgr is not null and e4.mgr = e5.empno
            and e5.mgr is not null and e5.mgr = e6.empno
      )
   );

Assertion EMP_MGR_FK_NO_CYCLE_AS created.

Assertion EMP_MGR_FK_NO_CYCLE_AS created.

Success!

This approach enforces acyclic hierarchies and additionally limits the number of hierarchical levels to 5.

For real-life applications, you may wish to increase the number of levels to more than 5. In any case, you should be able to decide on a suitable maximum number of levels. The number of joins will match the number of levels. The code for that is boring but not too complicated.

Test Cases

Let’s run some tests to verify if the SQL Assertion behaves as expected.

11) Test cases

set echo on
-- fails (cycle)
update emp
   set mgr = (select empno from emp where ename = 'SCOTT')
 where ename = 'KING';

-- fails (cycle)
update emp
   set mgr = (select empno from emp where ename = 'SMITH')
 where ename = 'JONES';

-- succeeds
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1000, 'LEVEL5', 'TESTER', (select empno from emp where ename = 'ADAMS'), date '2026-02-01', 1000, null, 10);

-- fails (6 levels, no cycle)
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1001, 'LEVEL6', 'TESTER', (select empno from emp where ename = 'LEVEL5'), date '2026-02-01', 1100, null, 20);

set echo on
-- fails (cycle)
update emp
   set mgr = (select empno from emp where ename = 'SCOTT')
 where ename = 'KING';

-- fails (cycle)
update emp
   set mgr = (select empno from emp where ename = 'SMITH')
 where ename = 'JONES';

-- succeeds
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1000, 'LEVEL5', 'TESTER', (select empno from emp where ename = 'ADAMS'), date '2026-02-01', 1000, null, 10);

-- fails (6 levels, no cycle)
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1001, 'LEVEL6', 'TESTER', (select empno from emp where ename = 'LEVEL5'), date '2026-02-01', 1100, null, 20);

SQL> -- fails (cycle)
SQL> update emp
  2  	set mgr = (select empno from emp where ename = 'SCOTT')
  3   where ename = 'KING';
update emp
*
ERROR at line 1:
ORA-08601: SQL assertion (DEMO.EMP_MGR_FK_NO_CYCLE_AS) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/


SQL> 
SQL> -- fails (cycle)
SQL> update emp
  2  	set mgr = (select empno from emp where ename = 'SMITH')
  3   where ename = 'JONES';
update emp
       *
ERROR at line 1:
ORA-08601: SQL assertion (DEMO.EMP_MGR_FK_NO_CYCLE_AS) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/


SQL> 
SQL> -- succeeds
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (1000, 'LEVEL5', 'TESTER', (select empno from emp where ename = 'ADAMS'), date '2026-02-01', 1000, null, 10);

1 row created.

SQL> 
SQL> -- fails (6 levels, no cycle)
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (1001, 'LEVEL6', 'TESTER', (select empno from emp where ename = 'LEVEL5'), date '2026-02-01', 1100, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
            *
ERROR at line 1:
ORA-08601: SQL assertion (DEMO.EMP_MGR_FK_NO_CYCLE_AS) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/

SQL> -- fails (cycle)
SQL> update emp
  2  	set mgr = (select empno from emp where ename = 'SCOTT')
  3   where ename = 'KING';
update emp
*
ERROR at line 1:
ORA-08601: SQL assertion (DEMO.EMP_MGR_FK_NO_CYCLE_AS) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/


SQL> 
SQL> -- fails (cycle)
SQL> update emp
  2  	set mgr = (select empno from emp where ename = 'SMITH')
  3   where ename = 'JONES';
update emp
       *
ERROR at line 1:
ORA-08601: SQL assertion (DEMO.EMP_MGR_FK_NO_CYCLE_AS) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/


SQL> 
SQL> -- succeeds
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (1000, 'LEVEL5', 'TESTER', (select empno from emp where ename = 'ADAMS'), date '2026-02-01', 1000, null, 10);

1 row created.

SQL> 
SQL> -- fails (6 levels, no cycle)
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (1001, 'LEVEL6', 'TESTER', (select empno from emp where ename = 'LEVEL5'), date '2026-02-01', 1100, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
            *
ERROR at line 1:
ORA-08601: SQL assertion (DEMO.EMP_MGR_FK_NO_CYCLE_AS) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/

Conclusion

SQL Assertions allow us to implement integrity rules that were previously difficult to enforce when taking factors such as concurrency and performance into account. They allow us to perform consistency checks in the database layer instead of relying on application logic.

Although the inability to use hierarchical queries within SQL Assertions is a significant limitation, the join-based approach presented in this post can effectively prevent hierarchical cycles and ensure consistency through a purely declarative solution. For most real-life data models, limiting the depth of the hierarchy should be a feasible workaround.

SQL Assertions close one of the last major gaps toward Codd’s principle of integrity independence (Rule 10 of Codd’s 12 rules), making declarative data integrity in the Oracle AI Database nearly complete.