Oracle获取外键

发布于 2024-10-30 16:24:32 字数 528 浏览 3 评论 0原文

我想获取模式中的所有外键，如下所示。假设我有表

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'

原文

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'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无所谓啦 2024-11-06 16:24:32

找到了！

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

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'

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'

回复收藏 0 原文

幽蝶幻影 2024-11-06 16:24:32

使用 @maephisto 解决方案会出现一个小错误：
如果源表主键是复合键，则运行查询将导致重复不必要的记录。

考虑 T1 和 T2 表：
主表 T1:

create table T1
(
  pk1 NUMBER not null,
  pk2 NUMBER not null
);
alter table T1
  add constraint T1PK primary key (PK1, PK2);

明细表 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);

@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'

在此处输入图像描述

Using @maephisto solution will case a little bug:
If the source tables primary key is a composite key then running the query will result duplicate unnecessary records.

Consider T1 and T2 tables:
Master table 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:

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:

enter image description here

To over come the problem the query bellow will serve:

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'

enter image description here

回复收藏 0 原文