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

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 Received on Mon Mar 30 2009 - 12:28:46 CDT