SQL 查询表的外键关系

Query a Table's Foreign Key relationships

提问by Mark Roddy

For a given table 'foo', I need a query to generate a set of tables that have foreign keys that point to foo. I'm using Oracle 10G.

对于给定的表 'foo'，我需要一个查询来生成一组具有指向 foo 的外键的表。我正在使用 Oracle 10G。

回答by Mike Monette

This should work (or something close):

这应该有效（或接近）：

select table_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 here>'); 

回答by Mark Roddy

The following statement should give the children and all of their descendents. I have tested it on an Oracle 10 database.

下面的语句应该给孩子们和他们所有的后代。我已经在 Oracle 10 数据库上对其进行了测试。

SELECT  level, main.table_name  parent,
    link.table_name child
FROM    user_constraints main, user_constraints link    
WHERE   main.constraint_type    IN ('P', 'U')
AND link.r_constraint_name  = main.constraint_name
START WITH main.table_name  LIKE UPPER('&&table_name')
CONNECT BY main.table_name = PRIOR link.table_name
ORDER BY level, main.table_name, link.table_name

回答by matt1616

Here's how to take Mike's query one step further to get the column namesfrom the constraint names:

以下是如何将 Mike 的查询更进一步以从约束名称中获取列名称：

select * from user_cons_columns
where constraint_name in (
  select 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 name here>'));

回答by arvinq

I know it's kinda late to answer but let me answer anyway, some of the answers above are quite complicated hence here is a much simpler take.

我知道现在回答有点晚了，但无论如何让我回答，上面的一些答案非常复杂，因此这里有一个简单得多的答案。

       `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'`

回答by Tony R

link to Oracle Database Online Documentation

链接到Oracle 数据库在线文档

You may want to explore the Data Dictionary views. They have the prefixes:

您可能想要探索数据字典视图。它们有以下前缀：

• User
• All
• DBA

• 用户
• 全部
• 数据库管理员

sample:

样本：

select * from dictionary where table_name like 'ALL%' 

---

---

Continuing Mike's example, you may want to generate scripts to enable/disable the constraints. I only modified the 'select' in the first row.

继续 Mike 的示例，您可能希望生成脚本来启用/禁用约束。我只修改了第一行中的“选择”。

select  'alter table ' || TABLE_NAME || ' disable constraint ' || 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 here>');

回答by Ethan Post

Download the Oracle Reference Guide for 10G which explains the data dictionary tables.

下载解释数据字典表的 Oracle 10G 参考指南。

The answers above are good but check out the other tables which may relate to constraints.

上面的答案很好，但请查看可能与约束相关的其他表格。

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%CONS%';

Finally, get a tool like Toad or SQL Developer which allows you to browse this stuff in a UI, you need to learn to use the tables but you should use a UI also.

最后，获得一个像 Toad 或 SQL Developer 这样的工具，它允许你在 UI 中浏览这些东西，你需要学习使用表格，但你也应该使用 UI。

回答by Hiram

select      acc.table_name, acc.constraint_name 
from        all_cons_columns acc
inner join all_constraints ac
    on acc.constraint_name = ac.constraint_name
where       ac.r_constraint_name in (
    select  constraint_name
    from    all_constraints
    where   table_name='yourTable'
    );

回答by Mehmet Kurt

All constraints for one table

一张表的所有约束

select 

    uc.OWNER,
    uc.constraint_name as TableConstraint1,
    uc.r_constraint_name as TableConstraint2,
    uc.constraint_type as constrainttype1,
    us.constraint_type as constrainttype2,
    uc.table_name as Table1,us.table_name as Table2,
    ucc.column_name as TableColumn1, 
    uccs.column_name as TableColumn2
from user_constraints uc
    left outer join user_constraints us on uc.r_constraint_name = us.constraint_name
    left outer join USER_CONS_COLUMNS ucc on ucc.constraint_name = uc.constraint_name
    left outer join USER_CONS_COLUMNS uccs on uccs.constraint_name = us.constraint_name
where uc.OWNER ='xxxx' and uc.table_name='xxxx' 

回答by Abu Turab

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;