Artful Common Queries

Find primary key of a table

from the Artful Common Queries page

To retrieve primary keys of db.tbl...

SELECT k.column_name 
FROM information_schema.table_constraints t 
JOIN information_schema.key_column_usage k 
USING (constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY' 
  AND t.table_schema='db'
  AND t.table_name='tbl'

For pre-5 versions of MySQL:

SHOW INDEX FROM tbl
WHERE key_name='primary';

Last updated 22 May 2024