Oracle 所有外键引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1171373/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle all foreign key references
提问by AJM
I've inherited a schema so don't have complete knowledge/confidence in what is there.
我继承了一个模式，所以对那里的内容没有完整的知识/信心。
I have a project table with a projectId. There are a whole bunch of other tables that reference this table by project id. What I want to do is run a query to establish:
我有一个带有 projectId 的项目表。还有一大堆其他表通过项目 ID 引用此表。我想要做的是运行一个查询来建立：
- Which tables have foreign key references to the project table on the project id
- Which tables have a column called project id (in case foreign keys are not set up).
- 哪些表具有对项目 id 上的项目表的外键引用
- 哪些表有一个名为 project id 的列（以防未设置外键）。
If it was SQL Server I know how to query the metadata but how do I do this in Oracle?
如果是 SQL Server，我知道如何查询元数据，但如何在 Oracle 中执行此操作？
回答by Rob van Wijk
1)
1)
select table_name
from all_constraints
where r_constraint_name = [your pk/uk constraint on PROJECTS(id)]
2)
2)
select table_name
from all_tab_columns
where column_name = 'PROJECT_ID'
You may want to add an extra predicate containing the OWNER column.
您可能想要添加一个包含 OWNER 列的额外谓词。
Regards, Rob.
问候，罗布。
回答by Stan
Ok. Here a request that give you the referenced table and column :
好的。这是一个为您提供引用表和列的请求：
SELECT
c_list.CONSTRAINT_NAME as NAME,
substr(c_src.COLUMN_NAME, 1, 20) as SRC_COLUMN,
c_dest.TABLE_NAME as DEST_TABLE,
substr(c_dest.COLUMN_NAME, 1, 20) as DEST_COLUMN
FROM ALL_CONSTRAINTS c_list, ALL_CONS_COLUMNS c_src, ALL_CONS_COLUMNS c_dest
WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME
AND c_list.OWNER = c_src.OWNER
AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
AND c_list.OWNER = c_dest.OWNER
AND c_list.CONSTRAINT_TYPE = 'R'
AND c_src.OWNER = '<your-schema-here>'
AND c_src.TABLE_NAME = '<your-table-here>'
GROUP BY c_list.CONSTRAINT_NAME, c_src.TABLE_NAME,
c_src.COLUMN_NAME, c_dest.TABLE_NAME, c_dest.COLUMN_NAME;
Which give you something like this:
这给了你这样的东西：
NAME |SRC_COLUMN |DEST_TABLE | DEST_COLUMN
----------------------|----------------|----------------------|-----------
CFK_RUB_FOR |FOR_URN |T03_FORMAT |FOR_URN
CFK_RUB_RUB |RUB_RUB_URN |T01_RUBRIQUE |RUB_URN
CFK_RUB_SUP |SUP_URN |T01_SUPPORT |SUP_URN
CFK_RUB_PRD |PRD_URN |T05_PRODUIT |PRD_URN
You can forget the substr() function if the result is usable without. This is not my case.
如果没有结果可用，您可以忘记 substr() 函数。这不是我的情况。
回答by Erich Kitzmueller
1): SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME='R' and R_CONSTRAINT_NAME='xxx'
1）： SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME='R' and R_CONSTRAINT_NAME='xxx'
where xxx is the name of the primary key constraint on the project table
其中 xxx 是项目表上的主键约束的名称
2): SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='PROJECT_ID'
2）： SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='PROJECT_ID'
回答by Brian
The r_constraint_nameanswers here didn't seem to work for me, not sure why as I'm new to Oracle myself, but this worked:
r_constraint_name这里的答案似乎对我不起作用，不知道为什么，因为我自己是 Oracle 的新手，但这有效：
SELECT * FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = '<constraint>';
回答by Spangen
My problem was slightly different. I have a table and I wanted to programatically know which other tables/columns it references.
我的问题略有不同。我有一个表，我想以编程方式知道它引用了哪些其他表/列。
I started with Stan's response above but this didn't give me exactly what I needed, so I came up with this, which I post here in case anyone else has my problem:
我从上面 Stan 的回答开始，但这并没有完全满足我的需求，所以我想出了这个，我把它贴在这里以防其他人遇到我的问题：
WITH src as
(SELECT ac.table_name, ac.constraint_name, accs.column_name, accs.position, ac.r_constraint_name
FROM ALL_CONSTRAINTS ac, all_cons_columns accs
WHERE ac.owner = '<owner>'
AND ac.constraint_type = 'R'
AND ac.table_name = '<src_table>'
AND accs.owner = ac.owner
AND accs.table_name = ac.table_name
AND accs.constraint_name = ac.constraint_name
ORDER BY ac.table_name, ac.constraint_name, accs.position),
dst as
(SELECT ac.table_name, ac.constraint_name, accs.column_name, accs.position
FROM ALL_CONSTRAINTS ac, all_cons_columns accs
WHERE ac.owner = '<owner>'
AND accs.owner = ac.owner
AND accs.table_name = ac.table_name
AND accs.constraint_name = ac.constraint_name
ORDER BY ac.table_name, ac.constraint_name, accs.position)
SELECT src.table_name as src_table,
dst.table_name as dst_table,
src.constraint_name as src_constraint,
src.column_name as src_column,
dst.column_name as dst_column,
src.position as position
FROM src,dst
WHERE src.r_constraint_name = dst.constraint_name
AND src.position = dst.position
回答by uhs
Use this query.
使用此查询。
select b.TABLE_NAME,b.CONSTRAINT_NAME ,a.COLUMN_NAME
from all_constraints b, all_cons_columns a
where r_constraint_name = 'Constraint_Name' and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME;
