Re: how can I determine the primary key for a table?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 30 Mar 2009 19:48:36 +0200
Message-ID: <49D105F4.2070602_at_gmail.com>

ddf schrieb:
> Comments embedded.
>
> On Mar 30, 11:20 am, "Larry W. Virden" <lvir..._at_gmail.com> wrote:

>> I have a set of oracle tables which were set up by people no longer
>> available for consultation.

>
> A good reason why the data dictionary contains the information it
> does.
>

>> When I select from the all_indexes table, asking about the index_name
>> for one of my tables, it lists:
>>
>> SQL> select index_name, uniqueness
>>         from all_indexes
>>         where table_name = 'TABLE_H'
>>         ;  2    3    4
>>
>> INDEX_NAME                     UNIQUENES
>> ------------------------------       ----------------
>> TABLE_H_PK                      UNIQUE
>>
>> However, TABLE_H doesn't have a column called TABLE_H_PK .

>
> Of course not, that's the INDEX name, not the column name.
>

>> How can I determine what column in TABLE_H is the primary key?

>
> One would use either the USER_IND_COLUMNS view (if you're connected as
> the owner) or the ALL_IND_COLUMNS view (if you're connected as someone
> else) to return the actual column name or names configured as the
> primary key:
>
> select index_name, column_name
> from all_ind_columns
> where index_name in
> (select index_name
> from all_indexes
> where table_name = 'TABLE_H'
> and uniqueness = 'UNIQUE')
> order by index_name, column_position;
>
>
> You'll retrieve one or more rows depending upon how many columns
> comprise the primary key. As an example:
>
> SQL> select index_name, column_name
> 2 from all_ind_columns
> 3 where index_name in
> 4 (select index_name
> 5 from all_indexes
> 6 where table_name = 'EMP'
> 7 and uniqueness = 'UNIQUE')
> 8 order by index_name, column_position;
>
> INDEX_NAME COLUMN_NAME
> ------------------------------ ------------------------------
> PK_EMP EMPNO
>
> SQL>
>
>
>
> David Fitzjarrell

I would like to provide some additional considerations:

1. primary key constraints can be maintained by nonunique index
2. primary key columns may be subset of corresponding index columns.

SQL> alter table emp drop primary key;

Table altered.

SQL>
SQL> create index emp_idx on emp(empno,ename);

Index created.

SQL>
SQL> select index_name from user_indexes where table_name = 'EMP';

INDEX_NAME

---

EMP_IDX SQL>
SQL> alter table emp add constraint emp_pk primary key(empno) using index;

Table altered.

SQL>
SQL>
SQL> select
   2  cons.table_name,
   3  cons.constraint_name,

   4 cons.index_name,
   5 cols.column_name
   6 from all_cons_columns cols,all_constraints cons    7 where cons.owner=cols.owner and

   8  cons.constraint_name=cols.constraint_name and
   9  cons.constraint_type='P' and
  10  cons.table_name = 'EMP';

TABLE_NAME CONSTRAINT INDEX_NAME COLUMN_NAM ---------- ---------- ---------- ---------- EMP EMP_PK EMP_IDX EMPNO SQL>
SQL> select column_name

   2 from all_ind_columns
   3 where index_name = 'EMP_IDX';

COLUMN_NAM

---

EMPNO
ENAME Best regards

Maxim Received on Mon Mar 30 2009 - 12:48:36 CDT