MySQL - DBI: How to tell if the first column is an auto increment column?

Question

Is it possible with this information to reliably tell if the first column of the table is an auto increment column?

The available information is as follows :

database handle ($dbh)
database name
table name

Answer 1

You can query table COLUMNS in the mysql information schema, using column EXTRA.

You would assume that an autoincremented column is of integer datatype, is not nullable and has no default value.

my $sql = q{SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = ?
    AND TABLE_NAME = ?
    AND ORDINAL_POSITION = 1
    AND DATA_TYPE = 'int'
    AND COLUMN_DEFAULT IS NULL
    AND IS_NULLABLE = 'NO'
    AND EXTRA like '%auto_increment%'};

my ($first_col_is_autoincrement) =         
   $dbh->selectrow_array( $sql, undef, $db_name, $table_name );

It is also probably possible to use the DBI catalog functions to achieve the same operation in a database-independent way.