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:
>
> A good reason why the data dictionary contains the information it
> does.
>
>
> Of course not, that's the INDEX name, not the column name.
>
>
> 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
EMP_IDX SQL>
SQL> alter table emp add constraint emp_pk primary key(empno) using index;
4 cons.index_name,
5 cols.column_name
6 from all_cons_columns cols,all_constraints cons 7 where cons.owner=cols.owner and
EMPNO
ENAME Best regards
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:
- primary key constraints can be maintained by nonunique index
- 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