oracle 如何将所有具有 FK 的表移到另一个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/731413/
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 get all tables that have FKs to another table?
提问by juan
Is there any way to get all tables that have foreign keys to another table in oracle with a query?
有什么办法可以通过查询将所有具有外键的表获取到oracle中的另一个表？
回答by crb
Here is a good articlewith an answer:
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name in (select constraint_name from all_constraints
where constraint_type in ('P','U') and table_name='TABLE_NAME');
回答by dpbradley
Assuming that both the parent and child tables are in the same schema do the following:
假设父表和子表都在同一个架构中，请执行以下操作：
select t1.table_name child_table, t1.constraint_name, t2.table_name parent_table
from user_constraints t1, user_constraints t2
where t1.r_constraint_name = t2.constraint_name
Note that r_constraint_name is populated only for FK (type 'R') constraints, so the self-join only returns info of interest
请注意，r_constraint_name 仅针对 FK（类型 'R'）约束填充，因此自联接仅返回感兴趣的信息
回答by Tamil Mani
if we know the parent key, just altered juan 's answer with employees table
如果我们知道父键，只需用员工表更改 juan 的答案
select *
from user_constraints
where R_CONSTRAINT_NAME='EMP_EMP_ID_PK'
and constraint_type='R'
