I'm not sure if the DBI primary_key methods are implemented in DBD::mysql. There is a MySQL-specific way to find the keys using $sth->{mysql_is_pri_key} like so:

#!perl -w
use strict;
use DBI;
my($db,$table)=('test','tmp1');
my $dbh=DBI->connect("dbi:mysql(RaiseError=1):dbname=$db");
$dbh->do(qq/DROP TABLE IF EXISTS $table/);
$dbh->do(qq/CREATE TABLE $table (id INTEGER PRIMARY KEY, name VARCHAR(
+10))/);
for my $field(qw( id name )) {
    my $sth = $dbh->prepare("SELECT $field FROM $table WHERE 1=0");
    $sth->execute;
    for my $k($sth->{'mysql_is_pri_key'}) {
        my $n = 'IS';
        $n .= " NOT" unless $k->[0];
        print "$field $n A PRIMARY KEY\n";
    }
    $sth->finish;
}
$dbh->do(qq/DROP TABLE $table/);
$dbh->disconnect;
[download]

This is an old question, but I saw that it was referenced on StackOverflow and had some bad comments.

You can get a list of columns by looking at 'NAME' and an array of primary flags by looking at 'mysql_is_pri_key'. Because multiple columns can be part of the primary, you need to look at each column in turn. This is the code I use in my generic table updater.

I am going to assume you already have a database handle ($dbh) and a table name ($table)

        my $sql = "select * from $table LIMIT 0,1";
        my $tnames = $dbh->prepare($sql);
           $tnames->execute();
        my $names = $tnames->{NAME};
        my $prime = $tnames->{'mysql_is_pri_key'};
        my $count = @{$names};
        foreach my $pos (0...($count-1)){
             print "Looking at column $names->[$pos] ";
             if($prime->[$pos] eq 1){
                   print "this column is part of the PRIMARY KEY\n";
                }else{
                   print "it is just a regular column\n";
                }
        }
[download]

I would use the DBI catalog methods instead, which provide a cross-DB way of finding the primary key (and other stuff) of tables.

CREATE TABLE foo(
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  letter char(1) NOT NULL,
  name text NOT NULL,
  num float NULL,
  PRIMARY KEY (id)
);
[download]

#!/usr/local/bin/perl -w
use DBI;
my $dbh = DBI->connect('dbi:mysql:test', "", "", { PrintError => 0 , R
+aiseError => 1});
END { $dbh->disconnect if $dbh }
use Data::Dumper;

my $sth = $dbh->prepare('DESCRIBE foo');
$sth->execute;
while(my $r = $sth->fetchrow_hashref) {
    print Dumper $r;
}
[download]

Actually, a different way using a different SQL query (which I was hoping to avoid, since that's dependent on parsing specific output):

my $keys_ref = $dbh->selectall_arrayref("SHOW KEYS FROM $table");     
+             
    
foreach my $row ( @{$keys_ref} ) {
    print "Primary key: $row->[4]"
        if $row->[2] eq 'PRIMARY';
}
[download]

It'd probably be more readable to use a selectall_hashref and (in the MySQL case) snag

$row->{'Column_name'} if $row->{'Key_name'} eq 'PRIMARY'
[download]

Edited by Chady -- added code tags.