How to get primary key column in Oracle?

Question

I need to get the name of the primary key column.

In the input, I only have the table name.

Richie · Accepted Answer · 2013-09-23T09:11:13.887

247

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 that 'TABLE_NAME' is in upper case since Oracle stores table names in upper case.

edited Sep 23 '13 at 09:11

answered Jan 26 '12 at 10:26

Richie

9,006
5
25
38

Thanks, Richie. One more question: how to encode 'P' ? I need to execute this with "EXECUTE IMMEDIATE". thnx again. – Kirill A. Jan 26 '12 at 10:34
4

I think you would just put two single quotes round it like this ''P'' – Greg Reynolds Jan 26 '12 at 13:18
Alternatively, you could [quote it](http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF00218) using `q`. – Burhan Ali May 21 '14 at 09:56
and constraint_type = 'R' for foreign key – Abinash Bishoyi Feb 04 '15 at 15:39
Worked for me when not having the schema prefix before the table! Thanks! – FearlessFuture Apr 17 '15 at 18:51
If you need this in order to DROP the CONSTRAINT, then also include this column in your select `cons.CONSTRAINT_NAME` – ACV Aug 30 '18 at 10:37
I get `invalid table name` error. (Where) do I need to prefix the schema in this code, if at all? – safex Jun 03 '20 at 11:07
@richie What does it mean when you do not get anything back? – cokedude Dec 21 '22 at 19:03

score 29 · Answer 2 · edited Apr 19 '15 at 13:27

29

Same as the answer from 'Richie' but a bit more concise.

Query for user constraints only

SELECT column_name FROM all_cons_columns WHERE constraint_name = (
  SELECT constraint_name FROM user_constraints 
  WHERE UPPER(table_name) = UPPER('tableName') AND CONSTRAINT_TYPE = 'P'
);

Query for all constraints

SELECT column_name FROM all_cons_columns WHERE constraint_name = (
  SELECT constraint_name FROM all_constraints 
  WHERE UPPER(table_name) = UPPER('tableName') AND CONSTRAINT_TYPE = 'P'
);

edited Apr 19 '15 at 13:27

Mat

202,337
40
393
406

answered Sep 22 '14 at 13:01

My-Name-Is

4,814
10
44
84

@FearlessFuture For me it worked well. Can you describe your problem a bit more expressive? – My-Name-Is Apr 17 '15 at 19:38
I don't get any results from this query, but I do get results from the query for the accepted answer. – FearlessFuture Apr 17 '15 at 19:41
2

@FearlessFuture I assume that the constraint you are searching for isn't a user constraint. Replace `user_constraints` by `all_constraints`. – My-Name-Is Apr 18 '15 at 18:11
This causes trouble if you have a table with the same name in two or more schemas - need to also include owner in the join: `SELECT owner, column_name, position FROM all_cons_columns WHERE (owner, constraint_name) in (SELECT owner, constraint_name FROM all_constraints WHERE UPPER(table_name) = UPPER('&tableName') AND CONSTRAINT_TYPE = 'P') order by owner, position;` – Mark Stewart Feb 14 '19 at 21:38
1

@MarkStewart I aggree the second Query will not work. But your solution is inefficient. Try: "SELECT cols.column_name AS KEY_STREAM, '=' AS KEY_CONDITION, cols.column_name as KEY_LOOKUP, '' AS KEY_STREAM2 FROM user_constraints cons, user_cons_columns cols WHERE cons.constraint_name = cols.constraint_name and CONSTRAINT_TYPE = 'P' and cons.table_name = '' and cons.table_name = cols.table_name;" – Alexander Heim Apr 07 '20 at 06:58
Why do I get tables with multiple primary keys, some of which are non-unique? I assume this is some very bad implementation of compound keys? – Maile Cupo Nov 19 '20 at 18:41
@MaileCup, you are working with a PK consisting of multiple columns (composite key), in order to identify a record uniquely. I wouldn't say that it's a bad practice, but my optinion is that it's much easier to work with technical generated IDs. – My-Name-Is Dec 07 '20 at 13:41

score 3 · Answer 3 · edited Jun 23 '22 at 09:17

3

Select constraint_name,constraint_type from user_constraints where table_name** **= ‘TABLE_NAME’ ;

(This will list the primary key and then)

Select column_name,position from user_cons_columns where constraint_name=’PK_XYZ’;

(This will give you the column, here PK_XYZ is the primay key name)

edited Jun 23 '22 at 09:17

Alexander Hartmaier

2,178
12
21

answered Apr 25 '13 at 14:00

Vipin Tiwari

31
1

score 3 · Answer 4 · edited May 24 '17 at 11:50

Try This Code Here I created a table for get primary key column in oracle which is called test and then query

create table test
(
id int,
name varchar2(20),
city varchar2(20),
phone int,
constraint pk_id_name_city primary key (id,name,city)
);

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 = 'TEST' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner  ORDER BY cols.table_name, cols.position;

score 0 · Answer 5 · answered Sep 09 '15 at 03:42

Save the following script as something like findPK.sql.

set verify off
accept TABLE_NAME char prompt 'Table name>'

SELECT cols.column_name
FROM all_constraints cons NATURAL JOIN all_cons_columns cols
WHERE cons.constraint_type = 'P' AND table_name = UPPER('&TABLE_NAME');

It can then be called using

@findPK

How to get primary key column in Oracle?

5 Answers5

Linked

Related