首页 文章

如何在oracle中找到所有外键约束？

提问于
浏览 次
0

在StackOverflow中，我可以找到关于how to find all foreign key constraints in SQL Server的帖子 .

我只能找到有关如何在Oralce（List of foreign keys and the tables they reference）中查找表的所有外键约束的帖子 .

我的问题是：如何列出所有表的所有外键约束，而不是Oracle中的表 . 我的愿望答案将是this one，但对于Oracle而言 .

附：我正在使用oralce 11g快递版 . 我的oracle GUI管理工具是Orace SQL开发人员 .

oracle foreign-keys

3 回答

  • 0

    我会这样做的;

    如果您希望它与同一行中的所有列聚合

    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';
    回复于
  • 0

    这个答案基于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';
    回复于
  • 1

    不要在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';
    回复于

相关问题