如何从Oracle视图中发现基础主（或唯一）键列 [英] How to discover the underlying primary (or unique) key columns from an Oracle view

问题描述

我想知道是否有可能为我发现Oracle视图中涉及的所有表的基本主（或唯一）键列。这里有一个例子来说明我的意思：

  CREATE TABLE t_a（
 id number（7），
主键（id）
）; 
 
 CREATE VIEW v_a AS 
 SELECT * FROM t_a; 
  

所以通过命名约定，我知道 v_a.id 实际上是底层 t_a 表的主键列。是否有任何方式通过使用系统视图正式发现此信息，例如 SYS.ALL_CONSTRAINTS ， SYS.USER_CONSTRAINTS

• 约束是不
  
  
  <


• 我不感兴趣的键本身，但在视图的列。

解决方案

您可以通过user_dependencies视图找到该信息：

  SQL> CREATE TABLE t_a 
 2（id number（7）
 3，主键（id）
 4）
 5 / 
 
创建表。 
 
 SQL> CREATE VIEW v_a AS SELECT * FROM t_a 
 2 / 
 
视图已创建。 
 
 SQL> select c.constraint_name 
 2 from user_dependencies d 
 3，all_constraints c 
 4其中d.name ='V_A'
 5和d.referenced_type ='TABLE'
 6和d.referenced_link_name为null 
 7和d.referenced_owner = c.owner 
 8和d.referenced_name = c.table_name 
 9和c.constraint_type ='P'
 10 / 
 
 CONSTRAINT_NAME 
 ------------------------------ 
 SYS_C0051559 
 
选择1行。 
  

请注意，

Rob。

EDIT ：对于可能的视图列名称，您可以使用此查询。请注意，不能保证您的视图中存在此类列。

  SQL>选择c.constraint_name 
 2，'V_'|| substr（c.table_name，3）|| '。'|| cc.column_name possible_view_column 
 3来自user_dependencies d 
 4，all_constraints c 
 5，all_cons_columns cc 
 6其中d.name ='V_A'
 7和d.referenced_type ='TABLE'
 8和d.referenced_link_name为null 
 9和d.referenced_owner = c.owner 
 10和d.referenced_name = c.table_name 
 11和c.constraint_type ='P'
 12和c.owner = cc.owner 
 13和c.constraint_name = cc.constraint_name 
 14 / 
 
 CONSTRAINT_NAME POSSIBLE_VIEW_COLUMN 
 ------------------------------ -------------------- ----------------------------------------- 
 SYS_C0051561 V_A.ID 
 
选择1行。 
  

I was wondering whether there is a possibility for me to discover the underlying primary (or unique) key columns for all tables involved in an Oracle view. Here's an example to show what I mean:

CREATE TABLE t_a (
  id number(7),
  primary key(id)
);

CREATE VIEW v_a AS
SELECT * FROM t_a;

So by naming convention, I know that v_a.id is actually the primary key column of the underlying t_a table. Is there any way of formally discovering this information by using system views, such as SYS.ALL_CONSTRAINTS, SYS.USER_CONSTRAINTS, etc?

N.B:

• The constraints are NOT on the view, but on the underlying table.
• I'm not interested in the keys themselves, but in the columns of the view.

解决方案

You can find that information via the user_dependencies view:

SQL> CREATE TABLE t_a
  2  (   id number(7)
  3  ,   primary key(id)
  4  )
  5  /

Table created.

SQL> CREATE VIEW v_a AS SELECT * FROM t_a
  2  /

View created.

SQL> select c.constraint_name
  2    from user_dependencies d
  3       , all_constraints c
  4   where d.name = 'V_A'
  5     and d.referenced_type = 'TABLE'
  6     and d.referenced_link_name is null
  7     and d.referenced_owner = c.owner
  8     and d.referenced_name = c.table_name
  9     and c.constraint_type = 'P'
 10  /

CONSTRAINT_NAME
------------------------------
SYS_C0051559

1 row selected.

Regards,
Rob.

EDIT: For possible view column names, you can use this query. Note there is no guarantee that such a column exists in your view.

SQL> select c.constraint_name
  2       , 'V_' || substr(c.table_name,3) || '.' || cc.column_name possible_view_column
  3    from user_dependencies d
  4       , all_constraints c
  5       , all_cons_columns cc
  6   where d.name = 'V_A'
  7     and d.referenced_type = 'TABLE'
  8     and d.referenced_link_name is null
  9     and d.referenced_owner = c.owner
 10     and d.referenced_name = c.table_name
 11     and c.constraint_type = 'P'
 12     and c.owner = cc.owner
 13     and c.constraint_name = cc.constraint_name
 14  /

CONSTRAINT_NAME                POSSIBLE_VIEW_COLUMN
------------------------------ -------------------------------------------------------------
SYS_C0051561                   V_A.ID

1 row selected.

这篇关于如何从Oracle视图中发现基础主（或唯一）键列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！