如何在 Oracle 中查找具有表外键的表？

How to find tables having foreign key to a table in Oracle?

提问by Walker

I plan to delete data from a table, I would like to know how many and which tables have a foreign key reference to this particular table in Oracle. As I will have to set the foreign keys to null. I would like to know list of all tables which have a FK to this particular table.

我打算从表中删除数据，我想知道有多少表以及哪些表在 Oracle 中具有对该特定表的外键引用。因为我必须将外键设置为空。我想知道对这个特定表有 FK 的所有表的列表。

回答by kupa

SELECT d.table_name,

       d.constraint_name "Primary Constraint Name",

       b.constraint_name "Referenced Constraint Name"

FROM user_constraints d,

     (SELECT c.constraint_name,

             c.r_constraint_name,

             c.table_name

      FROM user_constraints c 

      WHERE table_name='EMPLOYEES' --your table name instead of EMPLOYEES

      AND constraint_type='R') b

WHERE d.constraint_name=b.r_constraint_name

回答by jpmc26

SELECT
  FK.OWNER||'.'||FK.TABLE_NAME AS CHILD_TABLE,
  SRC.OWNER||'.'||SRC.TABLE_NAME AS PARENT_TABLE,
  FK.CONSTRAINT_NAME AS FK_CONSTRAINT,
  SRC.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT
FROM ALL_CONSTRAINTS FK
JOIN ALL_CONSTRAINTS SRC ON FK.R_CONSTRAINT_NAME = SRC.CONSTRAINT_NAME
WHERE
  FK.CONSTRAINT_TYPE = 'R'
  AND SRC.OWNER = 'MY_SCHEMA'
  AND SRC.TABLE_NAME = 'MY_TABLE';

I have a situation where the table I'm interested in isn't owned by the schema I was connecting as. So I needed to modify the query in the currently accepted answerto use ALL_CONSTRAINTSinstead of USER_CONSTRAINTS. In the process, I made a mistake, and I found the accepted answer to be very difficult to read so that I could fix it. (The lack of explanation didn't help.) As a result, I ended up coming up with my own query. It's basically the same, but I think it's a bit easier to grok.

我有一种情况，我感兴趣的表不属于我连接的模式。所以我需要修改当前接受的答案中的查询以使用ALL_CONSTRAINTS而不是USER_CONSTRAINTS. 在这个过程中，我犯了一个错误，我发现接受的答案很难读，所以我可以修复它。（缺乏解释无济于事。）因此，我最终提出了自己的查询。基本上是一样的，但我认为它更容易理解。

FK.CONSTRAINT_TYPE = 'R'filters down FKto a set of foreign key constraints, and the join pairs these foreign keys up with their "Referenced constraint". (The referenced constraint is usually the primary key of the "parent" table.) Finally, we filter down to the parent table we're interested in using SRC.OWNER = 'MY_SCHEMA' AND SRC.TABLE_NAME = 'MY_TABLE'.

FK.CONSTRAINT_TYPE = 'R'过滤FK到一组外键约束，连接将这些外键与其“引用约束”配对。（被引用的约束通常是“父”表的主键。）最后，我们过滤到我们有兴趣使用的父表SRC.OWNER = 'MY_SCHEMA' AND SRC.TABLE_NAME = 'MY_TABLE'。

Naturally, you can switch this to use USER_CONSTRAINTSif you wish; just remove the SRC.OWNERcheck and the OWNERprefixes in the SELECT.

当然，USER_CONSTRAINTS如果您愿意，您可以将其切换为使用；只是删除SRC.OWNER检查和OWNER在前缀SELECT。

回答by nanosoft

Below query will give all the foreign key constraints defined on TABLE_NAME:

下面的查询将给出 TABLE_NAME 上定义的所有外键约束：

select baseTable.* from all_constraints baseTable , all_constraints referentedTable 
    where baseTable.R_CONSTRAINT_NAME = referentedTable.CONSTRAINT_NAME
    and baseTable.constraint_type = 'R'
    and referentedTable.table_name = 'TABLE_NAME';

回答by Dgeyzonne

Maybe I misunderstood what Walker asked, but what I understood is : How to find tables that have a foreign key reference to a particular table (ex : EMPLOYEES).

也许我误解了 Walker 的要求，但我的理解是：如何找到具有特定表的外键引用的表（例如：EMPLOYEES）。

If I try Kupa's answer :

如果我尝试 Kupa 的回答：

select d.table_name,
       d.constraint_name "Primary Constraint Name",
       b.constraint_name "Referenced Constraint Name"

from user_constraints d,

     (select c.constraint_name,
             c.r_constraint_name,
             c.table_name
      from user_constraints c 
      where table_name='EMPLOYEES' --your table name instead of EMPLOYEES
      and constraint_type='R') b

where d.constraint_name=b.r_constraint_name

I get the tables on which EMPLOYEES have a foreign key reference to.

我得到了 EMPLOYEES 有外键引用的表。

EMPLOYEES.foreign_key => TABLES.primary_key

EMPLOYEES.foreign_key => TABLES.primary_key

---

---

See below the updated sql to retrieve the tables that have a foreign key reference to EMPLOYEES.

请参阅下面更新的 sql 以检索具有指向 EMPLOYEES 的外键引用的表。

TABLES.foreign_key => EMPLOYEES.primary_key

TABLES.foreign_key => EMPLOYEES.primary_key

select b.table_name "Table Name",
   b.constraint_name "Constraint Name",
   d.table_name "Referenced Table Name",
   d.constraint_name "Referenced Constraint Name"

from user_constraints d,

 (select c.constraint_name,
         c.r_constraint_name,
         c.table_name
  from user_constraints c
  where constraint_type='R') b

where d.table_name = 'EMPLOYEES' --your table name instead of EMPLOYEES
and b.r_constraint_name = d.constraint_name;

回答by Vlisisids Akis

If you also need the fields to be included:

如果您还需要包含字段：

select b.table_name      "Referencing Table", 
       b.CONSTRAINT_NAME "Referencing Constraint",
       (select wm_concat(column_name)
          from all_cons_columns
         where owner = b.owner
           and constraint_name = b.CONSTRAINT_NAME
       ) "Referencing Columns",
       a.CONSTRAINT_NAME         "Referenced Constraint",
       (select wm_concat(column_name)
          from all_cons_columns
         where owner = a.owner
           and constraint_name = a.CONSTRAINT_NAME
       ) "Referenced columns"
  from all_constraints a,
       all_constraints b
 where a.owner = b.r_owner
   and a.owner = '<<OWNER>>'
   and a.table_name = '<<TABLE_NAME>>'
   and a.constraint_type in ('P', 'U')
   and b.constraint_type = 'R'
   and b.R_CONSTRAINT_NAME = a.constraint_name

回答by NikhilP

SELECT CONSTRAINT_NAME from ALL_CONSTRAINTS WHERE OWNER= sys_context('userenv','current_schema') AND CONSTRAINT_TYPE='R';

回答by ALIRA

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

回答by Gaius

There is no need to do this step manually - you can just use a cascading delete.

无需手动执行此步骤 - 您只需使用级联删除即可。