CODEWITHSUNDEEP
mysql
asker
asker

Reputation: 2199

How to determine primary key of a specific SQL table?

I mean not mean by manually reading the output of show create table..., but by select ... so that the primary key name is output directly as the result?

Upvotes: 1

Views: 82

Answers (2)

Abimaran Kugathasan
Abimaran Kugathasan

Reputation: 32468

show index from CC_CSR_USER where Key_name = 'PRIMARY';

CC_CSR_USER is a table name. Have a look on the column_name field on the result, you will get that PRIMARY KEY column of the table.

Upvotes: 0

Derek
Derek

Reputation: 23228

The following query should give you the PKs - just plug in your table_schema and table_name at the bottom of the query.

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` = 'dbName'
    AND t.`TABLE_NAME` = 'tableName';

Upvotes: 3

Related Questions