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

From: ddf <oratune_at_msn.com>
Date: Mon, 30 Mar 2009 11:46:31 -0700 (PDT)
Message-ID: <242211bc-19e4-47d4-848e-633884842c1e_at_m24g2000vbp.googlegroups.com>

On Mar 30, 12:48 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

I do understand this, however the example the OP posted clearly shows a unique index enforcing the primary key so I tailored the example to his given conditions.

I'd prefer to not confuse the OP with facts he's not using.

David Fitzjarrell Received on Mon Mar 30 2009 - 13:46:31 CDT