How to get the primary key "column name" of a specific table in MySQL

Question

Our system creates a log of every table that was updated or inserted with new content, it saves the table name, the ID value of the updated row or the last id inserted and the timestamp of the event.

This is useful because we can check what is the latest table updated and refresh the information being displayed to the user as soon as a change occurs, but we don't have the column name of the ID saved on the log.

The problem is that we are programming case by case in php.

if($tableName == 'Clients'){ $idname = 'CID'; }

is there a way to just ask MySQL: give me the primary key column name of a specific table, something like:

SHOW COLUMN_NAME FROM CLEINTS WHERE KEY_NAME = 'PRIMARY KEY';

I remember I had used a query like this in the past, but I can't remember what it was, I have found some solutions for SQL but don't seem to work in MySQL or are way too complicated (using information_schema), the query that I am looking for is very simple, almost like the example I just gave.

Thanks

Answer 1

You can get the details from information_schema database. Use the following query:

SELECT COLUMN_NAME 
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'Your_table_name' 
  AND CONSTRAINT_NAME = 'PRIMARY'

Answer 2

You can get KEYS by using

SHOW KEYS FROM CLEINTS WHERE Key_name = 'PRIMARY'

another alternative with SHOW to get INDEXES as @nick mentioned in comment

SHOW INDEXES FROM CLEINTS WHERE Key_name = 'PRIMARY'

syntax:

SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

documentation link for more details.

Answer 3

Hello @multimediaxp i think this may help you.

SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'yourDBName')
  AND (`TABLE_NAME` = 'Clients')
  AND (`COLUMN_KEY` = 'PRI');

For more detail please see given link: http://mysql-0v34c10ck.blogspot.com/2011/05/better-way-to-get-primary-key-columns.html