查看 oracle 的关于主/外键约束的元数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3210743/
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
Viewing oracle's metadata about primary/foreign key constraints
提问by ssr532
Which table contains detailed information(For example the table the foreign key is referring to) about the constraints? The tables 'all_cons_columns' , 'all_constraints' contains only the name of the constraints which isn't very helpful. I am currently using dbms_metadata.get_ddl() but it doesn't work on all the databases.
哪个表包含有关约束的详细信息（例如外键所指的表）？表 'all_cons_columns' ， 'all_constraints' 只包含约束的名称，这不是很有帮助。我目前正在使用 dbms_metadata.get_ddl() 但它不适用于所有数据库。
Thanks.
谢谢。
回答by Tony Andrews
It is all in there: the column R_CONSTRAINT_NAME in ALL_CONSTRAINTS contains the name of the referenced PK/UK constraint for a foreign key. You can then look up that constraint to get the TABLE_NAME of the reference table.
一切都在那里：ALL_CONSTRAINTS 中的 R_CONSTRAINT_NAME 列包含引用的外键 PK/UK 约束的名称。然后，您可以查找该约束以获取引用表的 TABLE_NAME。
When looking at ALL_CONS_COLUMNS, the POSITION of the column in the foreign key will match the POSITION of the column in the primary/unique key.
查看 ALL_CONS_COLUMNS 时，外键中列的 POSITION 将匹配主键/唯一键中列的 POSITION。
回答by ema
In order to retrieve the foreign key and generate a script to create these, you can use the following query:
为了检索外键并生成脚本来创建这些外键，您可以使用以下查询：
SELECT
'ALTER TABLE ' || a.table_name || ' ADD CONSTRAINT ' || a.constraint_name
|| ' FOREIGN KEY (' || a.column_name || ') REFERENCES ' || jcol.table_name
|| ' (' || jcol.column_name || ');' as commandforeign
FROM
(SELECT
uc.table_name, uc.constraint_name, uc.r_constraint_name, col.column_name
FROM
USER_CONSTRAINTS uc, USER_CONS_COLUMNS col
WHERE
uc.constraint_type='R' and uc.constraint_name=col.constraint_name) a
INNER JOIN
USER_CONS_COLUMNS jcol
ON
a.r_constraint_name=jcol.constraint_name;
回答by devio
This statement lists tables, constraint names, and foreign key table names:
此语句列出表、约束名称和外键表名称：
select c.table_name,c.constraint_name, --c.r_constraint_name,
cc.table_name
from all_constraints c
inner join all_constraints cc on c.r_constraint_name = cc.constraint_name
回答by Micha? Niklas
Have a look at: Reverse Engineering a Data Model. Based on this I did a Python program that dumps Oracle db schema to text. There is PRIMARY_KEYS_INFO_SQLand FOREIGN_KEYS_INFO_SQLthat do what you are interested in.
看看：逆向工程数据模型。基于此，我做了一个Python 程序，将 Oracle db schema 转储为 text。有PRIMARY_KEYS_INFO_SQL并且FOREIGN_KEYS_INFO_SQL做你感兴趣的事情。
