Oracle数据库中外键及其所参照的表列表

Question

我想要找到一个查询，以返回一个表的所有外键列表以及它们所引用的表和列。我已经完成了一半的工作：

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 a.table_name=:TableName 
  and C.CONSTRAINT_TYPE = 'R'

但我仍然需要知道这个键引用了哪个表和主键。我应该如何获取？

- stimms

---

@MenelaosVergis的回答对我非常有帮助，很容易理解 - https://dev59.com/MXI-5IYBdhLWcg3wsKh4#15364469 - Benj

如果我正确理解这个问题（从TableName的角度），它正在询问TableName从自身向其他表格所做的外部引用。如果您想要另一个方向（指向TableName的内部引用[来自其他表格]），那么这将是这个其他问题/答案。 - cellepo

Answer 1

在表 ALL_CONSTRAINTS 的列 r_owner 和 r_constraint_name 中描述了引用的主键。这将提供您所需的信息：

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND a.table_name = :TableName

- Vincent Malgrat

---

请注意，上面的代码没有考虑到复合外键。请参考@Dougman下面的答案，了解如何考虑复合键。 - xkrz

@xkrz 复合外键，比如在多个列上定义的外键？我不明白它们为什么不会被上述查询考虑到！ - Vincent Malgrat

@VincentMalgrat，抱歉，是我的错误。我想尝试使用您的代码列出“TableName+ColumnName”而不是约束名，但这不是您的代码所做的。 - xkrz

让我感到困扰的是c.owner不是第一列。除此之外非常好 :) - roshan

@roshan 既然你提到了，这个列的顺序感觉有点奇怪 :) 显然五年前我不会想到这个答案会被这么多人看到！ - Vincent Malgrat

Answer 2

试试这个：

select *
from all_constraints
where r_constraint_name in (
    select constraint_name
    from all_constraints
    where table_name='YOUR_TABLE_NAME'
);

- Touchstone

---

虽然我不完全清楚原帖中的意图，但对我来说，这个答案非常完美（而且更简单），可以回答这个问题：“如何在Oracle中获取引用特定表的外键？”通过使用constraint_name，我可以进行分析。提示：添加“owner ='MY_SCHEMA_HERE'”以澄清结果。非常好。 - Diego1974

请注意，我认为'YOUR_TABLE_NAME'不能包含模式前缀（例如 - 这样不起作用：'your_schema.your_table_name'）。但是看起来@Diego1974关于owner的提示（我猜它应该被添加到where条件中）可以这样指定模式。 - cellepo

注意：r_owner与owner不同，我认为这里需要的是r_owner。 - cellepo

尽管这个答案可能与 OP 的问题方向相反，但这个答案可能更接近于另一个类似的问题（我认为该问题询问的是这个答案所针对的方向）。 - cellepo

Answer 3

以下是我们经常使用的一个通用脚本，非常方便。

请保存它 (@fkeys.sql) ，以便您可以直接执行。它将允许您按所有者和父表或子表进行搜索，并显示外键关系。当前脚本明确地将结果输出到 C:\SQLRPTS ，因此您需要创建该文件夹或将该行更改为您想要使用的位置。

REM ########################################################################
REM ##
REM ##   fkeys.sql
REM ##
REM ##   Displays the foreign key relationships
REM ##
REM #######################################################################

CLEAR BREAK
CLEAR COL
SET LINES 200
SET PAGES 54
SET NEWPAGE 0
SET WRAP OFF
SET VERIFY OFF
SET FEEDBACK OFF

break on table_name skip 2 on constraint_name on r_table_name skip 1

column CHILDCOL format a60 head 'CHILD COLUMN'
column PARENTCOL format a60 head 'PARENT COLUMN'
column constraint_name format a30 head 'FK CONSTRAINT NAME'
column delete_rule format a15
column bt noprint
column bo noprint

TTITLE LEFT _DATE CENTER 'FOREIGN KEY RELATIONSHIPS ON &new_prompt' RIGHT 'PAGE:'FORMAT 999 SQL.PNO SKIP 2

SPOOL C:\SQLRPTS\FKeys_&new_prompt
ACCEPT OWNER_NAME PROMPT 'Enter Table Owner (or blank for all): '
ACCEPT PARENT_TABLE_NAME PROMPT 'Enter Parent Table or leave blank for all: '
ACCEPT CHILD_TABLE_NAME PROMPT 'Enter Child Table or leave blank for all: '

  select b.owner || '.' || b.table_name || '.' || b.column_name CHILDCOL,
         b.position,
         c.owner || '.' || c.table_name || '.' || c.column_name PARENTCOL,
         a.constraint_name,
         a.delete_rule,
         b.table_name bt,
         b.owner bo
    from all_cons_columns b,
         all_cons_columns c,
         all_constraints a
   where b.constraint_name = a.constraint_name
     and a.owner           = b.owner
     and b.position        = c.position
     and c.constraint_name = a.r_constraint_name
     and c.owner           = a.r_owner
     and a.constraint_type = 'R'
     and c.owner      like case when upper('&OWNER_NAME') is null then '%'
                                else upper('&OWNER_NAME') end
     and c.table_name like case when upper('&PARENT_TABLE_NAME') is null then '%'
                                else upper('&PARENT_TABLE_NAME') end
     and b.table_name like case when upper('&CHILD_TABLE_NAME') is null then '%'
                                else upper('&CHILD_TABLE_NAME') end
order by 7,6,4,2
/
SPOOL OFF
TTITLE OFF
SET FEEDBACK ON
SET VERIFY ON
CLEAR BREAK
CLEAR COL
SET PAGES 24
SET LINES 100
SET NEWPAGE 1
UNDEF OWNER

- Doug Porter

Answer 4

这将遍历给定表和列的外键层次结构，并从子级和孙子级以及所有后代表返回列。它使用子查询将r_table_name和r_column_name添加到user_constraints中，然后使用它们连接行。

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 = 'MY_TABLE_NAME' and column_name = 'MY_COLUMN_NAME'  
connect by nocycle 
prior table_name = r_table_name 
and prior column_name = r_column_name;

- user2323623

Answer 5

这里有另一种解决方案。使用系统默认视图速度非常慢（在我的情况下约为10秒）。这种方法比那快得多（约为0.5秒）。

SELECT
    CONST.NAME AS CONSTRAINT_NAME,
    RCONST.NAME AS REF_CONSTRAINT_NAME,

    OBJ.NAME AS TABLE_NAME,
    COALESCE(ACOL.NAME, COL.NAME) AS COLUMN_NAME,
    CCOL.POS# AS POSITION,

    ROBJ.NAME AS REF_TABLE_NAME,
    COALESCE(RACOL.NAME, RCOL.NAME) AS REF_COLUMN_NAME,
    RCCOL.POS# AS REF_POSITION
FROM SYS.CON$ CONST
INNER JOIN SYS.CDEF$ CDEF ON CDEF.CON# = CONST.CON#
INNER JOIN SYS.CCOL$ CCOL ON CCOL.CON# = CONST.CON#
INNER JOIN SYS.COL$ COL  ON (CCOL.OBJ# = COL.OBJ#) AND (CCOL.INTCOL# = COL.INTCOL#)
INNER JOIN SYS.OBJ$ OBJ ON CCOL.OBJ# = OBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ ACOL ON (CCOL.OBJ# = ACOL.OBJ#) AND (CCOL.INTCOL# = ACOL.INTCOL#)

INNER JOIN SYS.CON$ RCONST ON RCONST.CON# = CDEF.RCON#
INNER JOIN SYS.CCOL$ RCCOL ON RCCOL.CON# = RCONST.CON#
INNER JOIN SYS.COL$ RCOL  ON (RCCOL.OBJ# = RCOL.OBJ#) AND (RCCOL.INTCOL# = RCOL.INTCOL#)
INNER JOIN SYS.OBJ$ ROBJ ON RCCOL.OBJ# = ROBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ RACOL  ON (RCCOL.OBJ# = RACOL.OBJ#) AND (RCCOL.INTCOL# = RACOL.INTCOL#)

WHERE CONST.OWNER# = userenv('SCHEMAID')
  AND RCONST.OWNER# = userenv('SCHEMAID')
  AND CDEF.TYPE# = 4  /* 'R' Referential/Foreign Key */;

- Ganbat Bayarbaatar

---

在Oracle 10g中，这对我不起作用。"_CURRENT_EDITION_OBJ"未被识别。 - StilesCrisis

嗨，将SYS."_CURRENT_EDITION_OBJ"替换为SYS.OBJ$。它可以在10g和11g上运行。并确保你有足够的权限。另外我已经使用了SYS.OBJ$更改了我的答案。 - Ganbat Bayarbaatar

如何在此语句中包含模式（OWNER）（例如字符串SYSTEM）？ - Adam Mrozek

Answer 6

我知道现在回答有点晚，但我还是想回答一下，因为以上一些答案过于复杂，所以我来说一个更简单的解释。

SELECT a.table_name child_table, a.column_name child_column, a.constraint_name, 
      b.table_name parent_table, b.column_name parent_column
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
 join all_cons_columns b on c.owner = b.owner and c.r_constraint_name = b.constraint_name
 WHERE c.constraint_type = 'R'
   AND a.table_name = 'your table name'

- arvinq

---

这个查询结果给我返回了多个重复的行。我加上了DISTINCT，问题就解决了。 - Ray K.

Answer 7

如果您需要用户的所有外键，请使用以下脚本。

SELECT a.constraint_name, a.table_name, a.column_name,  c.owner, 
       c_pk.table_name r_table_name,  b.column_name r_column_name
  FROM user_cons_columns a
  JOIN user_constraints c ON a.owner = c.owner
       AND a.constraint_name = c.constraint_name
  JOIN user_constraints c_pk ON c.r_owner = c_pk.owner
       AND c.r_constraint_name = c_pk.constraint_name
  JOIN user_cons_columns b ON C_PK.owner = b.owner
       AND  C_PK.CONSTRAINT_NAME = b.constraint_name AND b.POSITION = a.POSITION     
 WHERE c.constraint_type = 'R'

基于Vincent Malgrat的代码

- Menelaos Vergis

---

它似乎没有返回用户约束，只在TABLE_NAME和R_TABLE_NAME中返回WRM$_SNAPSHOT和WRM$_DATABASE_INSTANCE。 - instanceOfObject

Answer 8

可能有点晚了，但我希望我的回答对需要选择复合外键的人有所帮助。

SELECT
    "C"."CONSTRAINT_NAME",
    "C"."OWNER" AS "SCHEMA_NAME",
    "C"."TABLE_NAME",
    "COL"."COLUMN_NAME",
    "REF_COL"."OWNER" AS "REF_SCHEMA_NAME",
    "REF_COL"."TABLE_NAME" AS "REF_TABLE_NAME",
    "REF_COL"."COLUMN_NAME" AS "REF_COLUMN_NAME"
FROM
    "USER_CONSTRAINTS" "C"
INNER JOIN "USER_CONS_COLUMNS" "COL" ON "COL"."OWNER" = "C"."OWNER"
 AND "COL"."CONSTRAINT_NAME" = "C"."CONSTRAINT_NAME"
INNER JOIN "USER_CONS_COLUMNS" "REF_COL" ON "REF_COL"."OWNER" = "C"."R_OWNER"
 AND "REF_COL"."CONSTRAINT_NAME" = "C"."R_CONSTRAINT_NAME"
 AND "REF_COL"."POSITION" = "COL"."POSITION"
WHERE "C"."TABLE_NAME" = 'TableName' AND "C"."CONSTRAINT_TYPE" = 'R'

- D.Zotov

Answer 9

在我谦虚的意见中，我的版本更易读：

SELECT   PARENT.TABLE_NAME  "PARENT TABLE_NAME"
,        PARENT.CONSTRAINT_NAME  "PARENT PK CONSTRAINT"
,       '->' " "
,        CHILD.TABLE_NAME  "CHILD TABLE_NAME"
,        CHILD.COLUMN_NAME  "CHILD COLUMN_NAME"
,        CHILD.CONSTRAINT_NAME  "CHILD CONSTRAINT_NAME"
FROM     ALL_CONS_COLUMNS   CHILD
,        ALL_CONSTRAINTS   CT
,        ALL_CONSTRAINTS   PARENT
WHERE    CHILD.OWNER  =  CT.OWNER
AND      CT.CONSTRAINT_TYPE  = 'R'
AND      CHILD.CONSTRAINT_NAME  =  CT.CONSTRAINT_NAME 
AND      CT.R_OWNER  =  PARENT.OWNER
AND      CT.R_CONSTRAINT_NAME  =  PARENT.CONSTRAINT_NAME 
AND      CHILD.TABLE_NAME  = ::table -- table name variable
AND      CT.OWNER  = ::owner; -- schema variable, could not be needed

- Francisco M

---

为了使其正常工作，我不得不将::更改为:，并将table更改为tabl。 - ZygD

是的，你说得对，我是用WinSQL制作的，并且变量识别使用的是**::而不是：，就像在SQLDeveloper中一样，你只需要使用：**来标记文本作为变量。如果我的表述不够清晰，那我很抱歉。 - Francisco M

Answer 10

我使用了以下代码，它达到了我的目的 -

SELECT fk.owner, fk.table_name, col.column_name
FROM dba_constraints pk, dba_constraints fk, dba_cons_columns col
WHERE pk.constraint_name = fk.r_constraint_name
AND fk.constraint_name = col.constraint_name
AND pk.owner = col.owner
AND pk.owner = fk.owner
AND fk.constraint_type = 'R'   
AND pk.owner = sys_context('USERENV', 'CURRENT_SCHEMA') 
AND pk.table_name = :my_table
AND pk.constraint_type = 'P';

- Anwarul Kabir Khan