oracle获取外键

Oracle get foreign keys(oracle获取外键)

本文介绍了oracle获取外键的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想获取架构中的所有外键,就像这样.假设我有桌子

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)

地址(id,文本)

我在 users-address_id 上为地址中的 id 列定义了一个 FK.我应该如何编写一个返回 FK 列的查询,例如:用户,address_id,地址,id?

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 ?

谢谢!

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'

推荐答案

找到了!

这就是我要找的东西,谢谢大家的帮助.

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'

这篇关于oracle获取外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:oracle获取外键

基础教程推荐