9

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

multimediaxp
  • 9,348
  • 13
  • 49
  • 80
  • Maybe this helps: https://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table – Jens Sep 12 '18 at 05:51
  • 2
    Close: `SHOW INDEX FROM CLIENTS WHERE KEY_NAME = 'PRIMARY';` – Nick Sep 12 '18 at 05:52
  • 1
    Not bad, SHOW INDEX FROM... is good, almost there! That returns all the indexes, including key_Name 'primary' an the column name, but it returns too much information, I just want the primary key name. Thanks! – multimediaxp Sep 12 '18 at 05:54
  • 1
    try this: `SHOW KEYS FROM CLEINTS WHERE Key_name = 'PRIMARY'` – Jigar Shah Sep 12 '18 at 05:57
  • @Nick, if you can add that as a response, I might take it as a good answer, it is very very close and definitely useful – multimediaxp Sep 12 '18 at 05:57
  • @JigarShah this is all great, seriously add them as answers, I am looking something like: SHOW KEYS.column_name FROM CLIENTS WHERE Key_name = 'PRIMARY', maybe it is not possible, please add your answers – multimediaxp Sep 12 '18 at 06:00

3 Answers3

7

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'
multimediaxp
  • 9,348
  • 13
  • 49
  • 80
Vykintas
  • 633
  • 5
  • 16
  • This is very good! It is exactly the result that I wanted, maybe information_schema was required after all, Thanks! – multimediaxp Sep 12 '18 at 06:12
  • Up-vote the question if you think it might be useful for other users. Thanks! – multimediaxp Sep 12 '18 at 06:16
  • @multimediaxp Somehow I am getting three times the same column name (PK) for a particular table in my case. I have added a DISTINCT clause to this solution. Please accept the edit :) – Madhur Bhaiya Sep 12 '18 at 06:37
  • 1
    Madhur Bhaiya HOW??? We talking about MYSQL. ONLY one PRIMARY key per table and all keys MUST have unique names. – Vykintas Sep 12 '18 at 19:31
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.

Jigar Shah
  • 6,143
  • 2
  • 28
  • 41
1

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

Yagnesh Makwana
  • 301
  • 2
  • 11