关于sql：查询表的外键关系

Query a Table's Foreign Key relationships

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

这应该工作(或接近)：

1
2
3
4
5
6
7
8

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>');

以下陈述应该给予孩子及其所有后代。我在Oracle 10数据库上测试过它。

1
2
3
4
5
6
7
8

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

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

1
2
3
4
5
6
7
8
9
10

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>'));

我知道现在回答有点迟，但无论如何我都要回答，上面的一些答案非常复杂，因此这里更简单。

1
2
3
4
5
6
7

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

相关讨论

我认为这样做效果更好：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

选择a.table_name child_table，a.column_name child_column，a.constraint_name，b.table_name parent_table，b.column_name parent_column，来自user_cons_columns的a.position和a.owner = c上的JOIN user_constraints c。 owner和a.constraint_name = c.constraint_name在c.owner = b.owner和c.r_constraint_name = b.constraint_name和a.position = b.position上加入user_cons_columns b，其中c.constraint_type ='R'由a.constraint_name排序， a.table_name，a.column_name，b.table_name，b.column_name</li></ul>[/collapse]</div><hr>

链接到Oracle数据库在线文档



您可能想要浏览数据字典视图。他们有前缀：


<ul>

<li>
用户
</li>

<li>
所有
</li>

<li>
DBA
</li>

</ul>


样品：


[cc lang="sql"]SELECT * FROM dictionary WHERE TABLE_NAME LIKE 'ALL%'

继续Mike的示例，您可能希望生成脚本以启用/禁用约束。我只修改了第一行的'select'。

1
2
3
4
5
6
7
8

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>');

1
2
3
4
5
6
7
8
9

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'
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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;

下载适用于10G的Oracle参考指南，其中介绍了数据字典表。

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