Как найти зависимости внешнего ключа, указывающие на одну запись в Oracle?

Я всегда смотрю на внешние ключи для стартовой таблицы и возвращаюсь обратно. Инструменты БД обычно имеют узел зависимостей или ограничений. Я знаю, что у PL/SQL Developer есть способ увидеть FK, но я давно его не использовал, поэтому я не могу это объяснить...

просто замените XXXXXXXXXXXX на имя таблицы...

/* The following query lists all relationships */ 

select
 a.owner||'.'||a.table_name "Referenced Table"
,b.owner||'.'||b.table_name "Referenced by"
,b.constraint_name "Foreign Key"
from all_constraints a, all_constraints b 
where 
b.constraint_type = 'R'
and a.constraint_name = b.r_constraint_name 
and b.table_name='XXXXXXXXXXXX' -- Table name 
order by a.owner||'.'||a.table_name

Вот мое решение перечислить все ссылки на таблицу:

select
  src_cc.owner as src_owner,
  src_cc.table_name as src_table,
  src_cc.column_name as src_column,
  dest_cc.owner as dest_owner,
  dest_cc.table_name as dest_table,
  dest_cc.column_name as dest_column,
  c.constraint_name
from
  all_constraints c
inner join all_cons_columns dest_cc on
  c.r_constraint_name = dest_cc.constraint_name
  and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
  c.constraint_name = src_cc.constraint_name
  and c.owner = src_cc.owner
where
  c.constraint_type = 'R'
  and dest_cc.owner = 'MY_TARGET_SCHEMA'
  and dest_cc.table_name = 'MY_TARGET_TABLE'
  --and dest_cc.column_name = 'MY_OPTIONNAL_TARGET_COLUMN'
;

С помощью этого решения у вас также есть информация о том, какой столбец какой таблицы ссылается на какой столбец вашей целевой таблицы (и вы можете фильтровать его).

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

SELECT LPAD(' ',4*(LEVEL-1)) || table1 || ' <-- ' || table2 tables, table2_fkey
FROM
  (SELECT a.table_name table1, b.table_name table2, b.constraint_name table2_fkey
  FROM user_constraints a, user_constraints b 
  WHERE a.constraint_type IN('P', 'U') 
  AND b.constraint_type = 'R' 
  AND a.constraint_name = b.r_constraint_name 
  AND a.table_name != b.table_name
  AND b.table_name <> 'MYTABLE')
CONNECT BY PRIOR  table2 = table1 AND LEVEL <= 5
START WITH table1 = 'MYTABLE';

Это дает такой результат при использовании SHIPMENT как MYTABLE в моей базе данных:

SHIPMENT <-- ADDRESS
SHIPMENT <-- PACKING_LIST
    PACKING_LIST <-- PACKING_LIST_DETAILS
    PACKING_LIST <-- PACKING_UNIT
        PACKING_UNIT <-- PACKING_LIST_ITEM
    PACKING_LIST <-- PO_PACKING_LIST
...

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

SQL> declare
  2      n pls_integer;
  3      tot pls_integer := 0;
  4  begin
  5      for lrec in ( select table_name from user_constraints
  6                    where r_constraint_name = 'T23_PK' )
  7      loop
  8          execute immediate 'select count(*) from '||lrec.table_name
  9                              ||' where col2 = :1' into n using &&target_val;
 10          if n = 0 then
 11              dbms_output.put_line('No impact on '||lrec.table_name);
 12          else
 13              dbms_output.put_line('Uh oh! '||lrec.table_name||' has '||n||' hits!');
 14          end if;
 15          tot := tot + n;
 16      end loop;
 17      if tot = 0
 18      then
 19          delete from t23 where col2 = &&target_val;
 20          dbms_output.put_line('row deleted!');
 21      else
 22          dbms_output.put_line('delete aborted!');
 23      end if;
 24  end;
 25  /
Enter value for target_val: 6
No impact on T34
Uh oh! T42 has 2 hits!
No impact on T69
delete aborted!

PL/SQL procedure successfully completed.

SQL>

Этот пример немного обманывает. Имя целевого первичного ключа жестко закодировано, а ссылочный столбец имеет одно и то же имя во всех зависимых таблицах. Исправление этих проблем оставляется читателю в качестве упражнения ;)

Была аналогичная ситуация. В моем случае у меня была пара записей, которые оказались с одним и тем же идентификатором, отличающимся только регистром. Хотел проверить, какие зависимые записи существуют для каждой, чтобы узнать, какие проще всего удалить/обновить.

Следующее выводит все дочерние записи, указывающие на данную запись, для каждой дочерней таблицы с количеством для каждой комбинации таблицы/основной записи.

declare
  --
  -- Finds and prints out how many children there are per table and value for each value of a given field
  --

  -- Name of the table to base the query on  
  cTable      constant varchar2(20) := 'FOO';
  -- Name of the column to base the query on
  cCol        constant varchar2(10) := 'ID';
  -- Cursor to find interesting values (e.g. duplicates) in master table
  cursor cVals is
    select id 
    from foo f
    where exists (  select 1 from foo f2 
                    where upper(f.id) = upper(f2.id)
                    and f.rowid != f2.rowid );

  -- Everything below here should just work
  vNum        number(18,0);
  vSql        varchar2(4000);

  cOutColSize   number(2,0) := 30;

  cursor cReferencingTables is
    select
      consChild.table_name,
      consChild.constraint_name,
      colChild.column_name     
    from user_constraints consMast
    inner join user_constraints consChild on consMast.constraint_name = consChild.r_constraint_name  
    inner join USER_CONS_COLUMNS colChild on consChild.CONSTRAINT_NAME = colChild.CONSTRAINT_NAME
    inner join USER_CONS_COLUMNS colMast on colMast.CONSTRAINT_NAME = consMast.CONSTRAINT_NAME
    where consChild.constraint_type = 'R'      
      and consMast.table_name = cTable
      and colMast.column_name = cCol
    order by consMast.table_name, consChild.table_name;
begin

  dbms_output.put_line(
    rpad('Table', cOutColSize) || 
    rpad('Column', cOutColSize) || 
    rpad('Value', cOutColSize) || 
    rpad('Number', cOutColSize)
  );
  for rRef in cReferencingTables loop
    for rVals in cVals loop
      vSql := 'select count(1) from ' || rRef.table_name || ' where ' || rRef.column_name || ' = ''' || rVals.id || '''';
      execute immediate vSql into vNum;
      if vNum > 0 then
        dbms_output.put_line(
          rpad(rRef.table_name, cOutColSize) || 
          rpad(rRef.column_name, cOutColSize) || 
          rpad(rVals.id, cOutColSize) || 
          rpad(vNum, cOutColSize) );
      end if;
    end loop;
  end loop;
end;

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

   SELECT referenced_table
         ,MAX(lvl) for_deleting
         ,MIN(lvl) for_inserting
   FROM
         ( -- Hierarchy of dependencies
         SELECT LEVEL lvl
               ,t.table_name referenced_table
               ,b.table_name referenced_by
         FROM user_constraints A
         JOIN user_constraints b 
               ON  A.constraint_name = b.r_constraint_name
               and b.constraint_type = 'R'
         RIGHT JOIN user_tables t
               ON  t.table_name = A.table_name
         START WITH b.table_name IS NULL
         CONNECT BY b.table_name = PRIOR t.table_name
         )
   GROUP BY referenced_table
   ORDER BY for_deleting, for_inserting;

Ограничения Oracle используют индексы таблиц для ссылки на данные.
Чтобы узнать, какие таблицы ссылаются на одну таблицу, просто найдите индекс в обратном порядке.

/* Toggle ENABLED and DISABLE status for any referencing constraint: */ 

select 'ALTER TABLE '||b.owner||'.'||b.table_name||' '||
        decode(b.status, 'ENABLED', 'DISABLE ', 'ENABLE ')||
       'CONSTRAINT '||b.constraint_name||';' 
  from all_indexes a,
       all_constraints b
 where a.table_name='XXXXXXXXXXXX' -- Table name 
   and a.index_name = b.r_constraint_name;

Примечание. Отключение ссылок значительно сокращает время выполнения команд DML (обновление, удаление и вставка).

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

/* List which columns are referenced in each constraint */

select ' TABLE "'||b.owner||'.'||b.table_name||'"'||
        '('||listagg (c.column_name, ',') within group (order by c.column_name)||')'|| 
        ' FK "'||b.constraint_name||'" -> '||a.table_name||
        ' INDEX "'||a.index_name||'"'
        "REFERENCES"
  from all_indexes a,
       all_constraints b,
       all_cons_columns c
 where rtrim(a.table_name) like 'XXXXXXXXXXXX' -- Table name 
   and a.index_name = b.r_constraint_name
   and c.constraint_name = b.constraint_name
 group by b.owner, b.table_name, b.constraint_name, a.table_name, a.index_name
 order by 1;