I have a set of oracle tables which were set up by people no longer
available for consultation.
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 .

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

Comments embedded.

On Mar 30, 11:20*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) 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

On Mar 30, 1:28*pm, ddf <orat... (AT) msn (DOT) com> wrote:

Thank you so much. That is just what I was looking for.

ddf schrieb:

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

On Mar 30, 12:48*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
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

Here's a totally different approach:

select dbms_metadata.get_ddl('TABLE', 'TABLE_H')
from dual

Of course, in this case the user needs access to the package.

Kind regards

robert

ddf wrote:
Sorry to disagree - but the OP only has a unique index, with
a confusing name, which led you to believe there is a primary key
constraint. All the OP did was querying ALL_INDEXES.

An index, unique or not, is not the same as a constraint.

There's another view to be queried for constraints, as Maxim showed.
--

Regards,
Frank van Bortel

On Apr 15, 2:25*am, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
Yes, I was very surprised to discover an enterprise software package
had more than a thousand tables, none of which had a primary key. I
had no reason to look until I tried to implement materialized views
and got some obscure errors.

The app extends the data dictionary with its own, uses some Oracle
features, and does its own version of others, some of which are very
useful, and of course one is primary key integrity. This can be a
problem when using other tools to access the database, and one must
gingerly approach using sql - especially using sql for DDL must be
avoided, otherwise the apps view of the dictionary becomes
disjointed. Sometimes the rules for determining the "primary key" can
be confusing, and the primary key can change as a result of certain
DDL - the app language allows implicit domains to control joining
tables in code, an odd syntax to do outer joins and a number of
syntaxes to do natural joins are just a couple of the consequences.

Enquiring of the vendor, I got a response that I could go ahead and
add them if I wanted, Oracle's rules for primary key usage of indices
are well established. The person investigating seemed quite
enthusiastic and pleased someone would actually care about this. I
had been confused a couple of times as to why there were no 'P' type
constraints, but just chalked it up to something obvious I was
missing, never really thinking it through until it caused an actual
problem - namely, not being able to use an Oracle feature. One of
these days I'll try adding it to the tables in question, it would help
a repetitive I/O spike due to doofus coding I hastily put together
after wasting so much time on this, to push data elsewhere... I just
have a problem adding to >1000 tables, some of which may "change"
primary keys in the future...

jg
--
@home.com is bogus.
My alma mater mentioned in /. : http://www.allosphere.ucsb.edu/