CODEWITHSUNDEEP
sqloracle11g
web_novice118
web_novice118

Reputation: 111

How to query for a primary key in oracle 11g

Does anybody know how to query for a primary key of a table in Oracle 11g? I saw a similar question for SQL Server, but I've had no luck from trying the answers on that thread.

Thanks!

Upvotes: 7

Views: 29911

Answers (2)

Mandeep Singh Gill
Mandeep Singh Gill

Reputation: 1345

SELECT cols.table_name
  ||' - '
  ||cols.column_name primary_key
FROM all_constraints cons,
  all_cons_columns cols,
  user_tables ut
WHERE cons.constraint_type = 'P'
AND cons.constraint_name   = cols.constraint_name
AND cons.owner             = cols.owner
AND cols.table_name        = ut.table_name
ORDER BY cols.table_name;

Upvotes: 1

Jacob Schoen
Jacob Schoen

Reputation: 14202

As the user that owns the table you can do:

select constraint_name, status, deferrable, deferred, validated, generated 
from user_constraints 
where constraint_type = 'P' and table_name = 'Table Name'

Update: I think this gets you what you need.

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

You can check this site for more details.

Upvotes: 13

Related Questions