1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
sid_com
  • 24,137
  • 26
  • 96
  • 187

1 Answers1

2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Also, please add a link to the DBI catalog functions ( https://metacpan.org/pod/DBI#Catalog-Methods ) - these allow to do the same in a database-independent way ) – Corion Jan 05 '19 at 12:48
  • 1
    @Corion : right... I updated my answer to mention that, although I am unsure if DBI (and/or DBD::mysql) give access to all relevant columns in INFORMATION_SCHEMA.COLUMNS – GMB Jan 05 '19 at 13:16
  • @Corion: Do you know how this (find out if a column is an autoincrement column) is possible with the DBI catalog functions? – sid_com Jan 07 '19 at 05:34
  • @sid_com as far as I know there is no convenient way to get at the `auto_increment` status of a column. Checking the `extra` field to be `like '%auto_increment%'` is the only way to my knowledge. – Corion Jan 07 '19 at 09:01