database 如何在 Oracle 中查找指向一条记录的外键依赖项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2509512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find foreign-key dependencies pointing to one record in Oracle?
提问by daveslab
I have a very large Oracle database, with many many tables and millions of rows. I need to delete one of them, but want to make sure that dropping it will not break any other dependent rows that point to it as a foreign key record. Is there a way to get a list of all the other records, or at least table schemas, that point to this row? I know that I could just try to delete it myself, and catch the exception, but I won't be running the script myself and need it to run clean the first time through.
我有一个非常大的 Oracle 数据库，有许多表和数百万行。我需要删除其中一个，但要确保删除它不会破坏任何其他作为外键记录指向它的相关行。有没有办法获得指向这一行的所有其他记录的列表，或者至少是表模式？我知道我可以尝试自己删除它并捕获异常，但我不会自己运行脚本并且需要它在第一次运行时干净。
I have the tools SQL Developer from Oracle, and PL/SQL Developer from AllRoundAutomations at my disposal.
我可以使用 Oracle 的 SQL Developer 工具和 AllRoundAutomations 的 PL/SQL Developer 工具。
Thanks in advance!
提前致谢！
回答by zigarn
Here is my solution to list all references to a table:
这是我列出对表的所有引用的解决方案：
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'
;
With this solution you also have the information of which column of which table is referencing which column of your target table (and you can filter on it).
使用此解决方案，您还可以获得哪个表的哪一列引用目标表的哪一列的信息（您可以对其进行过滤）。
回答by Eric Schneider
I always look at the Foreign keys for the starting table and work my way back. The DB tools usually have a dependencies or constraints node. I know PL/SQL Developer has a way to see FK's, but it's been a while since I have used it, so I can't explain it...
我总是查看起始表的外键并返回。数据库工具通常有一个依赖项或约束节点。我知道 PL/SQL Developer 有一种查看 FK 的方法，但是我已经有一段时间没有使用它了，所以我无法解释它......
just replace XXXXXXXXXXXX with a table name...
只需将 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
回答by Donato Szilagyi
I had a similar problem recently, but experienced soon, that finding the direct dependencies is not enough. So I wrote a query to show a tree of multilevel foreign key dependencies:
我最近遇到了类似的问题，但很快就遇到了，找到直接依赖项是不够的。所以我写了一个查询来显示多级外键依赖关系树：
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';
It gives a result like this, when using SHIPMENT as MYTABLE in my database:
在我的数据库中使用 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
...
回答by APC
We can use the data dictionary to identify the tables which reference the primary key of the table in question. From that we can generate some dynamic SQL to query those tables for the value we want to zap:
我们可以使用数据字典来识别引用相关表主键的表。从中我们可以生成一些动态 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>
This example cheats a bit. The name of the target primary key is hardcoded, and the referencing column has the same name on all the dependent tables. Fixing these issues is left as an exercise for the reader ;)
这个例子有点作弊。目标主键的名称是硬编码的，并且所有依赖表上的引用列都具有相同的名称。解决这些问题留给读者作为练习；)
回答by Adam
Had a similar situation. In my case I had a couple of records which had ended up with the same ID differing only by case. Wanted to check what dependent records exists for each to know which was easiest to delete/update
有过类似的情况。在我的情况下，我有几条记录，它们的 ID 相同，仅因案例而异。想要检查每个存在哪些依赖记录以了解哪些最容易删除/更新
The following prints out all child records pointing to the given record, per child table with a count for each table/master record combination
以下打印出指向给定记录的所有子记录，每个子表以及每个表/主记录组合的计数
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;
回答by daivrz
I was surprised at how difficult it was to find the dependency order of tables based on foreign key relationships. I needed it because I wanted to delete the data from all tables and import it again. Here is the query I wrote to list the tables in dependency order. I was able to script the deletes using the query below, and import again using the results of the query in reverse order.
我很惊讶根据外键关系找到表的依赖顺序是多么困难。我需要它，因为我想从所有表中删除数据并再次导入。这是我编写的查询以按依赖顺序列出表。我能够使用下面的查询编写删除脚本，并使用查询结果以相反的顺序再次导入。
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;
回答by Luiz Vaz
Oracle constraints uses Table Indexes to reference data.
To find out what tables are referencing one table, just look for index in reverse order.
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;
Obs.: Disabling references improves considerably the time of DML commands (update, delete and insert).
观察：禁用引用可显着缩短 DML 命令（更新、删除和插入）的时间。
This can help a lot in bulk operations, where you know that all data is consistent.
这在批量操作中很有帮助，您知道所有数据都是一致的。
/* 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;
