Finding Circular References in a Table

Here's how to use a hierarchical query to detect circular references when inserting new rows into a self-referencing table.

If the entry causes a circular reference, I want to force a ROLLBACK.

PROCEDURE ...
IS
  e_infinite_loop EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_infinite_loop, -01436);   -- CONNECT BY loop in user data 

  v_cnt PLS_INTEGER; 

BEGIN

  SAVEPOINT circ;

  INSERT INTO dependency_link
              (   parent_id,   child_id
              )
  VALUES      ( p_parent_id, p_child_id
              );
              
--+-----------------------------------------------------------
--| You need to actually traverse the tree in order to 
--| detect circular dependencies.
--+-----------------------------------------------------------

            SELECT COUNT (*) cnt
              INTO v_cnt
              FROM dependency_link dl
        START WITH dl.child_id  = p_child_id
  CONNECT BY PRIOR dl.parent_id = dl.child_id;

EXCEPTION
  WHEN e_infinite_loop THEN
    put_long_line ('Loop detected!');

    ROLLBACK TO SAVEPOINT circ;
...

Specifying Join Criteria in a Natural Join

Since 9i, the Natural Join feature joins tables on all columns of the same name. You don't need to specify join criteria in the WHERE clause. In the following example, both tables have keys named 'PK':

DROP TABLE MASTER;
CREATE TABLE MASTER (pk NUMBER(10), mstr VARCHAR2(100));
-- primary key only needed to ENFORCE data integrity
ALTER TABLE MASTER ADD PRIMARY KEY (pk);

DROP TABLE slave;
CREATE TABLE slave (pk NUMBER(10), sstr VARCHAR2(50));

-- primary key only needed to ENFORCE data integrity
ALTER TABLE slave ADD primary KEY (pk);
-- foreign only needed to ENFORCE referential integrity
--ALTER TABLE slave add foreign key (pk) REFERENCES MASTER (pk);
--ALTER TABLE slave DROP constraint SYS_C002722;

INSERT INTO MASTER VALUES (1, 'M1');
INSERT INTO MASTER VALUES (2, 'M2');
INSERT INTO MASTER VALUES (3, 'M3');

INSERT INTO SLAVE VALUES (1,'S1-1');
INSERT INTO SLAVE VALUES (2,'S2-2');
INSERT INTO SLAVE VALUES (3,'S3-3');

COMMIT;

SELECT *
FROM MASTER NATURAL JOIN SLAVE
--WHERE slave.sstr LIKE 'S2%';

Note we did NOT get a Cartesian product -- only those rows matching.

If I had named the string columns the same in both tables, the SQL engine would have tried to match rows by that column as well.