Построение таблицы зависимостей с помощью рекурсивного запроса

Question

Я пытаюсь построить граф зависимостей таблиц на основе внешних ключей между ними. Этот график должен начинаться с произвольного имени таблицы в качестве корня. Я мог бы, учитывая имя таблицы, просмотреть таблицы, которые ссылаются на нее, используя представление all_constraints, затем просмотреть таблицы, которые ссылаются на них, и так далее, но это было бы ужасно неэффективно. Я написал рекурсивный запрос, который делает это для всех таблиц, но когда я добавляю:

START WITH Table_Name=:tablename

Это не возвращает все дерево.

Answer 1

    select parent, child, level from (
select parent_table.table_name parent, child_table.table_name child
 from user_tables      parent_table,
      user_constraints parent_constraint,
      user_constraints child_constraint,
      user_tables      child_table
where parent_table.table_name = parent_constraint.table_name
  and parent_constraint.constraint_type IN( 'P', 'U' )
  and child_constraint.r_constraint_name = parent_constraint.constraint_name
  and child_constraint.constraint_type   = 'R'
  and child_table.table_name = child_constraint.table_name
  and child_table.table_name != parent_table.table_name
)
start with parent = 'DEPT'
connect by prior child = parent

должно работать (замените имя таблицы, конечно), предполагая, что все находится в той же схеме. Используйте версии DBA_ таблиц словаря данных и условия для столбцов OWNER и R_OWNER, если вам нужно обрабатывать зависимости между схемами. При дальнейшем рассмотрении это не учитывает само-ссылочные ограничения (то есть ограничение на таблицу EMP, в которой столбец MGR ссылается на столбец EMPNO), поэтому вам придется изменить код для обработки этого случая, если вам нужно иметь дело с с самообращенными ограничениями.

В целях тестирования я добавил несколько новых таблиц в схему SCOTT, которые также ссылаются на таблицу DEPT (включая зависимость внуков)

SQL> create table dept_child2 (
  2  deptno number references dept( deptno )
  3  );

Table created.

SQL> create table dept_child3 (
  2    dept_child3_no number primary key,
  3    deptno number references dept( deptno )
  4  );

Table created.

SQL> create table dept_grandchild (
  2    dept_child3_no number references dept_child3( dept_child3_no )
  3  );

Table created.

и проверил, что запрос вернул ожидаемый результат

SQL> ed
Wrote file afiedt.buf

  1  select parent, child, level from (
  2  select parent_table.table_name parent, child_table.table_name child
  3   from user_tables      parent_table,
  4        user_constraints parent_constraint,
  5        user_constraints child_constraint,
  6        user_tables      child_table
  7  where parent_table.table_name = parent_constraint.table_name
  8    and parent_constraint.constraint_type IN( 'P', 'U' )
  9    and child_constraint.r_constraint_name = parent_constraint.constraint_name
 10    and child_constraint.constraint_type   = 'R'
 11    and child_table.table_name = child_constraint.table_name
 12    and child_table.table_name != parent_table.table_name
 13  )
 14  start with parent = 'DEPT'
 15* connect by prior child = parent
SQL> /

PARENT                         CHILD                               LEVEL
------------------------------ ------------------------------ ----------
DEPT                           DEPT_CHILD3                             1
DEPT_CHILD3                    DEPT_GRANDCHILD                         2
DEPT                           DEPT_CHILD2                             1
DEPT                           EMP                                     1

Answer 2

Простейший способ сделать это - скопировать всю информацию FK в простую таблицу из 2 столбцов (parent,child), а затем использовать следующий алгоритм:

while (rows left in that table)
  list = rows where table name exists in child but not in parent
  print list
  remove list from rows

это все. По сути, вы сначала печатаете и удаляете все узлы, которые ни от чего не зависят. После этого некоторые другие узлы освободятся, и вы сможете повторить процесс.

PS Убедитесь, что вы не вставляете самоссылающиеся таблицы в начальный список (child=parent)