How to find out what table's primary keys are using a select query

Question

Is it possible to run SELECT PIRMARY_KEY FROM SomeTable, where PRIMARY_KEY is a keyword that will automatically translate to SomeTable's primary key columns

I am using Oracle database

Answer 1

Try this:

select cc.column_name
from user_cons_columns cc
join user_constraints c on c.constraint_name = cc.constraint_name
where c.table_name = 'MYTABLE'
and c.constraint_type = 'P'
order by cc.position

You can read more about these and other useful data dictionary views in the Oracle Database Reference.

Answer 2

If you are asking "how do I figure out what the primary key is for any given table", then 'desc table_name' will show you all the details you need to know.

Answer 3

This is database specific. The information usually resides in a proprietary system table.

Most of the time, however, the database implementation is nice enough to provide you with a stored procedure to fetch this information. There are API's around that will provide you with database agnostic ways to get this information, but I suspect you are after something accessible directly from SQL. Post your particular database type and I'm sure someone will know.