Oracle获得外键 [英] Oracle get foreign keys
问题描述
假设我有表格
users(id,username,pass,address_id)
和
地址(id,text)
我已经在users-address_id上为地址中的id列定义了一个FK。
我应该如何编写一个查询来返回FK列:
users,address_id,addresses,id?
$ b
谢谢! p>
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'
pre
$ p $ 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
和c.r_constra int_name = c_pk.constraint_name
加入USER_CONS_COLUMNS uc上的uc.constraint_name = c.r_constraint_name
WHERE C.R_OWNER ='myschema'
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)
and
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 ?
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'
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'
这篇关于Oracle获得外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!