在 Oracle SQL Developer 中,如果我正在查看有关表的信息,我可以查看约束,这让我可以看到外键(以及由此表引用了哪些表),并且我可以查看依赖关系以查看包等参考表。但我不确定如何找到哪些表引用了该表。
例如,假设我正在查看 emp
表。还有另一个表 emp_dept
,它捕获哪些员工在哪些部门工作,它通过 emp
表的主键 emp_id
引用 emp
表。有没有办法(通过程序中的一些 UI 元素,而不是通过 SQL)找到 emp_dept
表引用 emp
表,而我不必知道 emp_dept
表存在?
不可以。Oracle SQL Developer 没有提供这样的选项。
您必须手动执行查询或使用其他工具(例如 PLSQL Developer 有这样的选项)。以下 SQL 是 PLSQL Developer 使用的 SQL:
select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in ('P', 'U')
and table_name = :r_table_name
and owner = :r_owner
)
order by table_name, constraint_name
其中 r_owner
是架构,r_table_name
是您要查找参考的表。名称区分大小写
请注意,因为在 Oracle SQL Developer 的报告选项卡上有选项“所有表/依赖项”,这是来自 ALL_DEPENDENCIES,它指的是“过程、包、函数、包主体和触发器之间的依赖关系可供当前用户,包括在没有任何数据库链接的情况下创建的视图的依赖关系。”。那么,这份报告对你的问题没有任何价值。
要将其作为扩展添加到 SQL Developer,请执行以下操作:
将以下代码保存到 xml 文件中(例如 fk_ref.xml):
<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[FK References]]></title>
<query>
<sql>
<![CDATA[select a.owner,
a.table_name,
a.constraint_name,
a.status
from all_constraints a
where a.constraint_type = 'R'
and exists(
select 1
from all_constraints
where constraint_name=a.r_constraint_name
and constraint_type in ('P', 'U')
and table_name = :OBJECT_NAME
and owner = :OBJECT_OWNER)
order by table_name, constraint_name]]>
</sql>
</query>
</item>
</items>
将扩展添加到 SQL Developer:工具 > 首选项数据库 > 用户定义的扩展 单击“添加行”按钮 在类型中选择“编辑器”,位置是您保存上面 xml 文件的位置单击“确定”然后重新启动 SQL Developer 导航到任何表并您现在应该在 SQL one 旁边看到一个附加选项卡,标记为 FK References,它显示了新的 FK 信息。参考http://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47sql-086233.html
and exists
之前添加了这个条件:and owner = :OBJECT_OWNER
。
describe books;
和 select * from books;
后,它不会在 Oracle sql developer VM 上显示 fk 引用选项卡。
在下面的查询中将 [Your TABLE] 替换为 emp
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='[YOUR TABLE]');
constraint_type in ('P','U')
是多余的,因为如果约束TOTO的constraint_type是'R',那么TOTO的r_constraint_name当然是引用表中'P'或'U'类型的约束的名称。没有必要指定它。您正在使用 IN
,所以它就像很多 OR
,我们只关心 OR 的唯一操作数,其计算结果为真。
您可以从 ALL_CONSTRAINTS
视图中查询:
SELECT table_name
FROM ALL_CONSTRAINTS
WHERE constraint_type = 'R' -- "Referential integrity"
AND r_constraint_name IN
( SELECT constraint_name
FROM ALL_CONSTRAINTS
WHERE table_name = 'EMP'
AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key"
);
2015 年 5 月发布的 SQL Developer 4.1 添加了一个模型选项卡,该选项卡显示表外键,这些表外键以实体关系图格式引用您的表。
这已经在产品中多年了——尽管它在 2011 年还没有出现在产品中。
但是,只需单击模型页面。
确保您至少使用 4.0 版(2013 年发布)才能访问此功能。
https://i.stack.imgur.com/bXEPx.png
SELECT DISTINCT table_name,
constraint_name,
column_name,
r_table_name,
position,
constraint_type
FROM (SELECT uc.table_name,
uc.constraint_name,
cols.column_name,
(SELECT table_name
FROM user_constraints
WHERE constraint_name = uc.r_constraint_name) r_table_name,
(SELECT column_name
FROM user_cons_columns
WHERE constraint_name = uc.r_constraint_name
AND position = cols.position) r_column_name,
cols.position,
uc.constraint_type
FROM user_constraints uc
inner join user_cons_columns cols
ON uc.constraint_name = cols.constraint_name
WHERE constraint_type != 'C')
START WITH table_name = '&&tableName'
AND column_name = '&&columnName'
CONNECT BY NOCYCLE PRIOR table_name = r_table_name
AND PRIOR column_name = r_column_name;
像这样的东西怎么样:
SELECT c.constraint_name, c.constraint_type, c2.constraint_name, c2.constraint_type, c2.table_name
FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
WHERE c.table_name = <TABLE_OF_INTEREST>
AND c.constraint_TYPE = 'R';
dba_constraints
更改为 all_constraints
时,这对我有用:SELECT c.constraint_name, c.constraint_type, c2.constraint_name, c2.constraint_type, c2.table_name FROM all_constraints c JOIN all_constraints c2 ON (c.r_constraint_name = c2.constraint_name) WHERE c.table_name = '<TABLE_OF_INTEREST>' AND c.constraint_TYPE = 'R';
要添加到 sql developer 插件的上述答案,使用下面的 xml 将有助于获取与外键关联的列。
<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[FK References]]></title>
<query>
<sql>
<![CDATA[select a.owner,
a.constraint_name,
a.table_name,
b.column_name,
a.status
from all_constraints a
join all_cons_columns b ON b.constraint_name = a.constraint_name
where a.constraint_type = 'R'
and exists(
select 1
from all_constraints
where constraint_name=a.r_constraint_name
and constraint_type in ('P', 'U')
and table_name = :OBJECT_NAME
and owner = :OBJECT_OWNER)
order by table_name, constraint_name]]>
</sql>
</query>
</item>
</items>
我喜欢使用直接的 SQL 查询来完成此操作,而不是使用 SQL Developer 应用程序。
这是我刚刚做到的。最好通读一遍并了解发生了什么,这样您就可以对其进行调整以满足您的需求......
WITH all_primary_keys AS (
SELECT constraint_name AS pk_name,
table_name
FROM all_constraints
WHERE owner = USER
AND constraint_type = 'P'
)
SELECT ac.table_name || ' table has a foreign key called ' || upper(ac.constraint_name)
|| ' which references the primary key ' || upper(ac.r_constraint_name) || ' on table ' || apk.table_name AS foreign_keys
FROM all_constraints ac
LEFT JOIN all_primary_keys apk
ON ac.r_constraint_name = apk.pk_name
WHERE ac.owner = USER
AND ac.constraint_type = 'R'
AND ac.table_name = nvl(upper(:table_name), ac.table_name)
ORDER BY ac.table_name, ac.constraint_name
;
仅将 table_name 替换为您的主表名
select *
from all_constraints
where r_constraint_name in (
select constraint_name
from all_constraints
where table_name='table_name'
);
不定期副业成功案例分享