CODEWITHSUNDEEP
mysqlperlauto-incrementdbidbd-mysql
sid_com
sid_com

Reputation: 25107

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

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

Upvotes: 1

Views: 131

Answers (1)

GMB
GMB

Reputation: 222382

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.

Upvotes: 2

Related Questions