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

Question

I was wondering if I have the opportunity to discover the basic primary (or unique) key columns for all the tables involved in the Oracle view. Here is 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 t_a base table. Is there a way to formally discover this information using system views like SYS.ALL_CONSTRAINTS , SYS.USER_CONSTRAINTS , etc.

NB:

• Limitations are NOT in the view, but in the table below.
• I am not interested in the keys themselves, but in the view columns.

Answer 1

You can find this information through 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. 

Yours faithfully,
Rob

EDIT . For possible list column names, you can use this query. Please note that 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. 

Answer 2

 SELECT column_name FROM user_ind_columns WHERE index_name = (SELECT index_name FROM user_constraints WHERE constraint_type='P' AND table_name='T_A') ORDER BY column_position; 

If you want to do this for all the tables that the view depends on, then change the condition on table_name to something like:

 table_name IN (SELECT referenced_name FROM user_dependencies WHERE name='view_name' AND referenced_type='TABLE')