Oracle 获取外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5535990/
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 get foreign keys
提问by maephisto
I'd like to get all foreign keys in a schema, like this. Let's say I have tables
我想在架构中获取所有外键，就像这样。假设我有桌子
users(id, username, pass, address_id)
users(id, username, pass, address_id)
and
和
addresses(id, text)
addresses(id, text)
I have defined a FK on users-address_id to the id column in addresses. How should I write a query that would return me the FK columns like : users, address_id, addresses, id ?
我在 users-address_id 上定义了一个 FK 到地址中的 id 列。我应该如何编写一个查询来返回 FK 列，例如：users, address_id,addresses, id？
Thanks!
谢谢！
SELECT *
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.R_OWNER = 'TRWBI'
回答by maephisto
found it!
找到了！
this is what i was looking for, thanks everybody for helping.
这就是我要找的，谢谢大家的帮助。
SELECT a.table_name, a.column_name, uc.table_name, uc.column_name
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
join USER_CONS_COLUMNS uc on uc.constraint_name = c.r_constraint_name
WHERE C.R_OWNER = 'myschema'
回答by Mohsen Heydari
Using @maephisto solution will case a little bug:
If the source tables primary key is a composite keythen running the query will result duplicate unnecessary records.
Consider T1 and T2 tables:
Master table T1:
使用@maehisto 解决方案会出现一个小错误：
如果source tables primary key is a composite key然后运行查询将导致duplicate unnecessary records.
考虑 T1 和 T2 表：
主表 T1：
create table T1
(
pk1 NUMBER not null,
pk2 NUMBER not null
);
alter table T1
add constraint T1PK primary key (PK1, PK2);
Detail table T2:
明细表T2：
create table T2
(
pk1 NUMBER,
pk2 NUMBER,
name1 VARCHAR2(100)
);
alter table T2
add constraint T2FK foreign key (PK1, PK2)
references T1 (PK1, PK2);
The result of the @maephisto query will be:
To over come the problem the query bellow will serve:
@maephisto 查询的结果将是：
为了克服下面的查询将服务的问题：
SELECT master_table.TABLE_NAME MASTER_TABLE_NAME,
master_table.column_name MASTER_KEY_COLUMN,
detail_table.TABLE_NAME DETAIL_TABLE_NAME,
detail_table.column_name DETAIL_COLUMN
FROM user_constraints constraint_info,
user_cons_columns detail_table,
user_cons_columns master_table
WHERE constraint_info.constraint_name = detail_table.constraint_name
AND constraint_info.r_constraint_name = master_table.constraint_name
AND detail_table.POSITION = master_table.POSITION
AND constraint_info.constraint_type = 'R'
AND constraint_info.OWNER = 'MY_SCHEMA'
回答by a_horse_with_no_name
They are listed in the system view ALL_CONSTRAINTS
它们列在系统视图中 ALL_CONSTRAINTS
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022
Edit
编辑
The columns involved in the constraints are listed in ALL_CONS_COLUMNS
约束中涉及的列列在 ALL_CONS_COLUMNS
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1035.htm#i1575870
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1035.htm#i1575870
