sql - 甲骨文获得外键

我想像这样在架构中获取所有外键。
假设我有 table
users(id, username, pass, address_id)
和
addresses(id, text)
我已经在users-address_id上定义了一个FK到地址中的id列。
我应该如何写一个查询，使我返回FK列，如:
用户，address_id，地址，id？

谢谢!

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'

最佳答案

找到了!

这就是我一直在寻找的东西，谢谢大家的帮助。

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'

关于sql - 甲骨文获得外键，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5535990/