How to discover the underlying primary (or unique) key columns from an Oracle view

database, oracle, primary-key, schema, view

Solution

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.

Problem

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.

Original source