How can I get the auto incrementing field name or the primary key fieldname from a mysql table?

Question

In PHP, how do I get the field name of the field that's been set as to auto increment when a new rec is added to it?

In most cases, it's the same as the PRIMARY_KEY of the table but not necessarily always.

So this question has 2 parts with the second one branching into a 3rd part.

1- How to get the name of the auto-incrementing field name...

2- How to get the name of the primary_key field name...

2.1 How to get the primary_key(s) info when a table uses more than one field as its primary key...

score 6 · Answer 1 · answered Jun 19 '12 at 02:48

6

if you want to get the primary key column of the table, you can use this code:

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
    AND t.table_schema=DATABASE() 
    AND t.table_name='tbName';    -- the name of your table

To get the auto-incremented field, try this:

SELECT Auto_increment 
FROM information_schema.tables 
WHERE table_name   = 'tbName'  
  AND table_schema = DATABASE();

answered Jun 19 '12 at 02:48

Clint Ceballos

151
3

when querying the information_schema table, use innodb_stats_on_metadata=0 to have better performances: https://www.percona.com/blog/2011/12/23/solving-information_schema-slowness/ – ling Feb 11 '16 at 08:36
The statement meant to return the name of the auto_increment column actually returns the auto_increment value, thus not answering the question. – Benedikt Jan 06 '21 at 18:25

score 3 · Accepted Answer · answered Jun 19 '12 at 02:54

3

You can get the table information using the SHOW COLUMNS FROM table. Something like this:

$res = $mysqli->query('SHOW COLUMNS FROM tablename');

while($row = $res->fetch_assoc()) {
  if ($row['Extra'] == 'auto_increment')
    echo 'Field with auto_increment = '.$row['Field'];
  if ($row['Key'] == 'PRI')
    echo 'Field with primary key = '.$row['Field'];
}

answered Jun 19 '12 at 02:54

flowfree

16,356
12
52
76

1

show columns from tablename where Extra='auto_increment' – ling Feb 11 '16 at 08:47

icanc · Answer 3 · 2012-06-19T03:02:42.977

You can get those info by using the SHOW COLUMNS command. More info

Example: Say you have a table named City. The query to see the table attributes would be:

mysql> SHOW COLUMNS FROM City;

...And the result:
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

This is from http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

I'd upvote this answer if you actually included the command to get the information the OP asked for. — JohnFx, Jun 19 '12 at 02:44

score 2 · Answer 4 · answered Jun 19 '12 at 02:46

You can query the information_schema database:

SELECT column_name, column_key, extra 
FROM information_schema.columns 
WHERE table_schema=DATABASE() AND table_name='tablename';

The column_key will consist of the key type, i.e. PRI, MUL, etc.
The extra column will contain auto_increment for the auto increment column.

Note that the information_schema database is "global" so you must always pass the respective database (either specifically or via DATABASE() for the current database) and table, otherwise you end up with a BIG result set.

How can I get the auto incrementing field name or the primary key fieldname from a mysql table?

4 Answers4

Linked