How to check primary key in a view

[rk458p]

I want to list all the attributes that have constraint as primary key in a view

[rski]

You mean, you have a view and you want to know which columns in that view are primary keys?

[rk458p]

yes, I have a view and want to know which columns in that view are primary keys?

[rski]

I'm afraid orcale do not write that information but:
you can try to parse text column from user_views and check columns you get from here
or do something similar to that

select distinct t1.table_name, t1.column_name,t2.referenced_name,t3.column_name,t4.constraint_name,t5.constraint_type from 
    user_tab_columns t1
    ,user_dependencies t2
    ,user_tab_columns t3 
    ,user_cons_columns t4
    ,user_constraints t5
where 
    t1.table_name='<VIEW_NAME>'
    and t1.table_name=t2.name
   and t2.referenced_name=t3.table_name
   and t1.column_name=t3.column_name
   and t4.table_name=t3.table_name
   and t4.column_name=t3.column_name
   and t4.constraint_name=t5.constraint_name
   and t5.constraint_type='P'
 

but this query has some limits.

[magicwand]

Are you sure, you are looking for the primary key of a view ?

The reason I'm asking is, that constraints on views have a different implementation than constraints on i.e. tables:

Constraints on views are used in data warehousing to ease the work of the optimizer and are NOT ENFORCED and NOT VALIDATED by Oracle (to be exact: they are only enforced and validated if they are a subset of a constraint on the table and the table constraint is enabled, which is pretty trivial).

If that is what you want, just can query users_constraints:

SQL> create table test (a number constraint pk_test primary key, b varchar2(10));
Table created.

SQL> create view v_test as select * from test;
View created.

SQL> alter view v_test add constraint pk_v_test primary key (a) disable novalidate;
View altered.

SQL> select constraint_name, constraint_type
2 from user_constraints where table_name = 'V_TEST';

CONSTRAINT_NAME C
------------------------------ -
PK_V_TEST P

If you are looking for the primary keys of the UNDERLYING tables, you might want to look into this (if you are working with views on tables in other schemas, you have to use the all_* views instead of the users_* views in the query below):

SQL> create table test_detail
2 ( id number constraint pk_test_detail primary key
3 ,det_col varchar2(10)
4 ,test_id number constraint fk_test references test(a)
5 );
Table created.

SQL> create or replace view v_test as
2 select a,b,id,detail from test t, test_detail d where t.a = d.id;
View created.

SQL> set col "TAB.COL" for a50
SQL> select cc.table_name || '.' || cc.column_name "TAB.COL"
2 from user_constraints c
3 ,user_cons_columns cc
4 ,user_dependencies d
5 where d.name = 'V_TEST'
6 and c.table_name = d.referenced_name
7 and c.constraint_type = 'P'
8 and cc.owner = c.owner
9 and cc.constraint_name = c.constraint_name
10 ;

TAB.COL
--------------------------
TEST.A
TEST_DETAIL.ID