MySQL - How to get primary key of a table?

Question

Can anyone please tell me how to get the primary key of a table in MySQL?

Possible duplicate - see https://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query — Kevin Böhmer, Mar 19 '18 at 07:15
This is a duplicate. Please use the search function next time. -> https://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table — MBijen, Mar 19 '18 at 07:15
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Mar 19 '18 at 07:16
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) — MBijen, Mar 19 '18 at 07:16
See the answer from @DineshDB or you could use: `show index from TableName where Key_name = 'PRIMARY' ;` — MBijen, Mar 19 '18 at 07:22
@MBijen the above query isn't working. Error - "FAILED: ParseException line 1:11 mismatched input 'from' expecting ON near 'index' in show statement" — Anubhav Sarangi, Mar 19 '18 at 07:28
@AnubhavSarangi Sorry it should be indexes instead of index. `SHOW INDEXES FROM TableName WHERE Key_name = "PRIMARY";` — MBijen, Mar 19 '18 at 07:33
@AnubhavSarangi - I have tested it .it is working for me and it should work — user3040610, Mar 19 '18 at 07:37
Possible duplicate of [Get primary key of table?](https://stackoverflow.com/questions/2341278/get-primary-key-of-table) — JJJ, Mar 19 '18 at 07:50

DineshDB · Accepted Answer · 2018-03-19T07:39:43.480

8

Try this:

Solution 1:

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';

Solution 2:

This is from alexn

SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'

Solution 3:

SHOW INDEX FROM presort.final_conf_score_mld_run2 
WHERE Key_name = 'PRIMARY';

edited Mar 19 '18 at 07:39

answered Mar 19 '18 at 07:19

DineshDB

5,998
7
33
49

None of them is working....Can you just help me with the query - if my table name is "final_conf_score_mld_run2" and database name is "presort", what should be the query according to solution 1? – Anubhav Sarangi Mar 19 '18 at 07:28
Also if we can view the schema of the table - it will show which column is PRIMARY KEY. How to view the schema? – Anubhav Sarangi Mar 19 '18 at 07:32
@AnubhavSarangi, Try the third one. – DineshDB Mar 19 '18 at 07:32
Solution 3 also not working.... Error - "FAILED: ParseException line 1:11 mismatched input 'FROM' expecting ON near 'INDEX' in show statement" – Anubhav Sarangi Mar 19 '18 at 07:34
1

@AnubhavSarangi Are you sure you are using mysql? Since all of them should work. – MBijen Mar 19 '18 at 07:35
Sorry @MBijen @DineshDB - the solution 3 worked.... slight change in query - SHOW INDEX FROM presort.final_conf_score_mld_run2 WHERE `Key_name` = 'PRIMARY'; – Anubhav Sarangi Mar 19 '18 at 07:37

MySQL - How to get primary key of a table?

1 Answers1