Retrieving key attribute names of a given table

Question

Hi all,

I am working on a database middleware application again. I want to retrieve the names of all key values of a given table.

For instance:

CREATE TABLE foo(A INTEGER PRIMARY KEY, B VARCHAR(255))

I want to ask Oracle for the primary keys of 'foo' and I want to get 'A'.

Is this possible in Oracle at all? I tried to search their documentation but wasn't able to find anything of value.

Thanks in advance

Kage

Answer 1

Source:

SELECT       cols.table_name
             , cols.column_name
             , cols.position
             , cons.status
             , cons.owner
FROM         all_constraints cons
             , all_cons_columns cols
WHERE        cols.table_name = 'TABLE_NAME'
AND          cons.constraint_type = 'P'
AND          cons.constraint_name = cols.constraint_name
AND          cons.owner = cols.owner
ORDER BY     cols.table_name, cols.position;

Make sure to type the table_name in uppercase, as Oracle stores all table names in uppercase. Let's quickly explain the output from this query. table_name is the name of the table (stored in uppercase). column_name is the name of the column that is a part of the primary key. (also stored in uppercase) position is the position in the primary key. A primary key can contain more than one column, so understanding the order of the columns in the primary key is very important. status indicates whether the primary key is currently enabled or disabled. owner indicates the schema that owns the table.

ALL_CONSTRAINTS - here is a very good description of ALL_CONSTRAINTS

ALL_CONS_COLUMNS - here is a very good description of ALL_CONS_COLUMNS