SQL query to find Primary Key of a table?

Question

How can I find which column is the primary key of a table by using a query?

You should definitely provide more detail in case of further questions, and use appropriate tags to add some context to the question. In this question, for example, the answer can be very different, depending on the specific database you are using. — João Silva, Sep 11 '12 at 23:35
http://stackoverflow.com/questions/893874/mysql-determine-tables-primary-key-dynamically — Mangesh, May 22 '16 at 10:45
Possible duplicate of [How do you list the primary key of a SQL Server table?](https://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table) — Løiten, Feb 08 '19 at 11:49
This will get you the primary key in SQL Server `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '' AND SUBSTRING(CONSTRAINT_NAME,1,2)='PK'` — Rich, May 03 '23 at 14:51

score 28 · Answer 1 · edited May 23 '17 at 11:54

28

This is a duplicate question:

credit to Lukmdo for this answer:

It might be not advised but works just fine:

show index from TABLE where Key_name = 'PRIMARY' ;

The solid way is to use information_schema:

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
    AND t.table_schema=DATABASE()
    AND t.table_name='owalog';

edited May 23 '17 at 11:54

Community

1
1

answered Sep 11 '12 at 23:20

ajon

7,868
11
48
86

Why do you assume it's MySQL? – João Silva Sep 11 '12 at 23:20
1

True, this applies at least to MySQL – ajon Sep 11 '12 at 23:21

score 15 · Accepted Answer · edited Oct 26 '17 at 04:35

15

For Oracle, you can look it up in the ALL_CONSTRAINTS table:

SELECT a.COLUMN_NAME
FROM all_cons_columns a INNER JOIN all_constraints c 
     ON a.constraint_name = c.constraint_name 
WHERE c.table_name = 'TBL'
  AND c.constraint_type = 'P';

DEMO.

For SQL Server, it was already answered here, and for MySQL check @ajon's answer.

edited Oct 26 '17 at 04:35

Nathan Davis

5,636
27
39

answered Sep 11 '12 at 23:25

João Silva

89,303
29
152
158

score 11 · Answer 3 · answered Feb 29 '16 at 11:12

11

For MySQL:

SELECT GROUP_CONCAT(COLUMN_NAME), TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  TABLE_SCHEMA = '**database name**'
  AND CONSTRAINT_NAME='PRIMARY'
GROUP BY TABLE_NAME;

Warning a primary key with two columns will have them separated by a coma (,)

answered Feb 29 '16 at 11:12

Romuald Brunet

5,595
4
38
34

That gives me this error: `'GROUP_CONCAT' is not a recognized built-in function name.` – mherzl Nov 09 '18 at 21:32

score 7 · Answer 4 · edited Sep 14 '12 at 05:26

7

Try this query in SQL server:

SELECT     X.NAME AS INDEXNAME,
           COL_NAME(IC.OBJECT_ID,IC.COLUMN_ID) AS COLUMNNAME
FROM       SYS.INDEXES  X 
INNER JOIN SYS.INDEX_COLUMNS  IC 
        ON X.OBJECT_ID = IC.OBJECT_ID
       AND X.INDEX_ID = IC.INDEX_ID
WHERE      X.IS_PRIMARY_KEY = 1
  AND      OBJECT_NAME(IC.OBJECT_ID)='YOUR_TABLE'

edited Sep 14 '12 at 05:26

Himanshu

31,810
31
111
133

answered Sep 12 '12 at 02:45

user1577417

126
3

score 3 · Answer 5 · answered Jun 09 '15 at 06:26

3

The following query gives the list of all the primary keys in the given database.

SELECT DISTINCT TABLE_NAME ,column_name
    FROM INFORMATION_SCHEMA.key_column_usage
    WHERE TABLE_SCHEMA IN ('*your_db_name*');

answered Jun 09 '15 at 06:26

BhandariS

606
8
20

1

Not strictly true, it will return all unique keys, not only primaries – Romuald Brunet Feb 29 '16 at 10:47

score 3 · Answer 6 · answered Sep 27 '18 at 12:22

3

In mySQL

SHOW COLUMNS FROM `table_name`;

This show the details of the columns ie field, data-type, key etc.

answered Sep 27 '18 at 12:22

Brian Wangombe

39
2

1

That gives me this error: `Incorrect syntax near the keyword 'FROM'.` – mherzl Nov 09 '18 at 21:39

score 1 · Answer 7 · answered Feb 26 '21 at 14:03

1

It's more easy way to do it:

DESC TABLE;

It shows you the structure of your TABLE;

answered Feb 26 '21 at 14:03

Maksim Yurchak

21
3

score 0 · Answer 8 · edited Apr 28 '22 at 18:05

0

LEFT joining table_constraints seems to take more time on mine. i use Information_schema.COLUMNS

SELECT TABLE_SCHEMA , TABLE_NAME , COLUMN_NAME 
FROM Information_schema.Columns 
WHERE COLUMN_KEY = "PRI" AND TABLE_SCHEMA = DATABASE()

edited Apr 28 '22 at 18:05

Kuro Neko

795
12
19

answered Feb 16 '20 at 05:18

Peter Lionhart

31
2

what was that about an EDIT you made ? – Peter Lionhart Feb 17 '20 at 06:36

score 0 · Answer 9 · edited Apr 27 '22 at 10:56

0

This this on SSMS -----

SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.key_column_usage
WHERE TABLE_NAME='YOUR_TABLE'------

edited Apr 27 '22 at 10:56

Kuro Neko

795
12
19

answered Apr 24 '22 at 20:25

Razel Marquez Bigayan

35
7

score 0 · Answer 10 · answered May 03 '23 at 06:53

0

For MS SQL Sever easy way to get the Primary key for the table

EXEC sp_pkeys TableName;

ex: EXEC sp_pkeys Users;

answered May 03 '23 at 06:53

Shareef

11
1

To get all keys use below query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Users'; – Shareef May 03 '23 at 06:58

Ashish Thombre · Answer 11 · 2020-08-02T10:26:58.280

-1

For MSSQL: SELECT DISTINCT C.TABLE_NAME,C.COLUMN_NAME,C.DATA_TYPE FROM sys.indexes I INNER JOIN sys.index_columns IC ON I.OBJECT_ID = IC.OBJECT_ID AND I.INDEX_ID = IC.INDEX_ID INNER JOIN information_schema.columns C ON COL_NAME(IC.OBJECT_ID,IC.COLUMN_ID) = C.COLUMN_NAME WHERE I.IS_PRIMARY_KEY = 1

edited Aug 02 '20 at 10:26

answered Aug 02 '20 at 10:09

Ashish Thombre

11
3

SQL query to find Primary Key of a table?

11 Answers11

Linked