sql - jerarquicas - Crear un gráfico de dependencia de tabla con una consulta recursiva

consulta recursiva sql

Question

Estoy intentando construir un gráfico de dependencia de tablas basado en las claves externas entre ellos. Este gráfico debe comenzar con un nombre de tabla arbitrario como su raíz. Podría, dado el nombre de una tabla, buscar las tablas que hacen referencia a ella usando la vista all_constraints, luego buscar las tablas que hacen referencia a ellas, y así sucesivamente, pero esto sería terriblemente ineficiente. Escribí una consulta recursiva que hace esto para todas las tablas, pero cuando agrego:

START WITH Table_Name=:tablename

No devuelve todo el árbol.

Answer 1

La forma más sencilla de hacerlo es copiar toda la información de FK en una tabla simple de 2 columnas (principal, secundaria) y luego usar el siguiente algoritmo:

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

eso es todo. Básicamente, primero imprime y elimina todos los nodos que no dependen de nada. Una vez hecho esto, algunos otros nodos se liberarán y podrá repetir el proceso.

PD Asegúrese de no insertar tablas autorreferenciales en la lista inicial (hijo = padre)

Answer 2

 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

debería funcionar (reemplazar el nombre de la tabla, por supuesto) suponiendo que todo está en el mismo esquema. Utilice las versiones DBA_ de las tablas y condiciones del diccionario de datos para las columnas PROPIETARIO y R_PRIMA si necesita manejar dependencias de esquema cruzado. En una mayor reflexión, esto no tiene en cuenta las restricciones autorreferenciales (es decir, una restricción en la tabla EMP que la columna MGR hace referencia a la columna EMPNO) tampoco, por lo que tendría que modificar el código para manejar ese caso si necesita tratar con restricciones autorreferenciales.

Para fines de prueba, agregué algunas tablas nuevas al esquema SCOTT que también hacen referencia a la tabla DEPT (incluida la dependencia de un nieto)

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.

y verificó que la consulta devolvió el resultado esperado

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