如何在oracle中找到所有外键约束？
如何在oracle中找到所有外键约束？
提问于 2017-07-30 17:39:42
在StackOverflow中，我可以找到关于how to find all foreign key constraints in SQL Server的帖子。
我只能找到关于如何在Oralce (List of foreign keys and the tables they reference)中找到表的所有外键约束的帖子。
我的问题是:如何列出所有表的所有外键约束，而不是Oracle中的表。我想要的答案将是类似于this one，但对于甲骨文。
我正在使用oralce 11g特快专递版。我的oracle GUI管理工具是Orace SQL developer。
回答 4
Stack Overflow用户
回答已采纳
发布于 2017-07-30 18:17:13
我会那样做的；
如果希望将其与同一行中的所有列聚合在一起
select distinct c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name,
listagg(c2.column_name,',') WITHIN GROUP ( ORDER BY C2.POSITION) OVER ( PARTITION BY c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name ) column_list
from dba_constraints c1
JOIN dba_cons_columns c2
ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner
where C1.constraint_type = 'R'或者像这样，如果您想要每列一行
select c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, c2.column_name
from dba_constraints c1
JOIN dba_cons_columns c2
ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner
where C1.constraint_type = 'R'
order by c1.owner, c1.table_name, c1.constraint_name, c2.position或者如果要引用列详细信息和引用列详细信息，则为
with constraint_colum_list as ( select owner, table_name, constraint_name, listagg(column_name,',') WITHIN GROUP ( order by position ) as column_list
FROM DBA_CONS_COLUMNS GROUP BY owner, table_name, constraint_name )
select distinct c1.owner, c1.table_name, c1.constraint_name, c2.column_list, c3.owner, c3.table_name, c3.constraint_name, c3.column_list
from DBA_constraints c1
JOIN constraint_colum_list c2 ON c1.CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.owner=c2.owner
JOIN constraint_colum_list c3 ON C1.R_CONSTRAINT_NAME=C3.CONSTRAINT_NAME AND C1.R_OWNER=C3.owner
where C1.constraint_type = 'R'
-- AND c1.owner = 'YOUR_SCHEMA';Stack Overflow用户
发布于 2017-07-30 17:50:34
不要在where条件中使用表名，您将得到所有表的FK约束。
SELECT A.TABLE_NAME,
A.COLUMN_NAME,
A.CONSTRAINT_NAME,
C.OWNER
FROM ALL_CONS_COLUMNS A,
ALL_CONSTRAINTS C
WHERE A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND C.CONSTRAINT_TYPE = 'R'
AND C.OWNER = 'MyDBLoginName';Stack Overflow用户
发布于 2017-07-31 02:19:41
这个答案是基于LauDec的解决方案。
因为我只需要我/用户指定的所有外键约束，所以我将使用以下SQL：
with constraint_colum_list as ( select owner, table_name, constraint_name, listagg(column_name,',') WITHIN GROUP ( order by position ) as column_list
FROM USER_CONS_COLUMNS GROUP BY owner, table_name, constraint_name )
select distinct c1.owner, c1.table_name, c1.constraint_name, c2.column_list, c3.owner, c3.table_name, c3.constraint_name, c3.column_list
from USER_CONSTRAINTS c1
JOIN constraint_colum_list c2 ON c1.CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.owner=c2.owner
JOIN constraint_colum_list c3 ON C1.R_CONSTRAINT_NAME=C3.CONSTRAINT_NAME AND C1.R_OWNER=C3.owner
where C1.constraint_type = 'R'; 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45402780
复制相关文章
相似问题
腾讯云开发者
