How to get primary key of table?


Question

Is there a way to get the name of primary key field from mysql-database? For example:

I have a table like this:

+----+------+
| id | name |
+----+------+
| 1  | Foo1 |
| 2  | Foo2 |
| 3  | Foo3 |
+----+------+

Where the field id is primary key (it has auto increment but I can't use that). How can I retrieve fields name "id" in php?

1
66
6/25/2018 9:41:27 PM

Accepted Answer

A better way is to use SHOW KEYS since you don't always have access to information_schema. The following works:

SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'

Column_name will contain the name of the primary key.

109
2/26/2010 12:03:15 PM

Here is the Primary key Column Name

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='YourDatabase'
  AND t.table_name='YourTable';

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon