Re: Oracle 9i View's Primary Key constraint

On 19 Jan 2006 17:50:08 -0800, warren.liyongbo_at_gmail.com wrote:

>In Oracle 9i we can create primary key constraint for views.
>
>I am writing a PL/SQL script to retrieve the primary key columns from
>Oracle data dictionaries. I can successfully do that with tables, but
>with views it is not possible with the same script, because the primary
>key constraint of views don't exist in the ALL_INDEXES and
>ALL_IND_COLUMNS.
 That's because there's no index backing the constraint, as there is a with a table.

 But the columns still appear in ALL_CONS_COLUMNS - which is also true for primary key constraints on tables.

SQL> create or replace view v as select dummy from dual;

View created

SQL> alter view v add constraint v_pk primary key (dummy) rely disable novalidate;

View altered

SQL> select * from user_cons_columns;

OWNER                          CONSTRAINT_NAME                TABLE_NAME

------------------------------ ------------------------------
------------------------------
--------------------------------------------------------------------------------
----------
TEST                           V_PK                           V DUMMY 1

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool