Find foreign key information using Perl / DBI / MySQL / InnoDB

Question

I want to programmatically find foreign keys in a specific InnoDB table in my MySQL database.

I am using Perl and I came across $dbh->foreign_key_info. I just tried using it, but it seems a bit wrong.

It does not return ON DELETE and ON UPDATE information, even if it implies that it is possible. And it also returns regular indexes.

Thanks for any help.

use strict;
use warnings;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect("DBI:mysql:database=db;host=localhost", "user", "password");
my $sth = $dbh->foreign_key_info(undef, undef, undef, undef, undef, "table_name");
print Dumper $sth->fetchall_hashref("FK_NAME");

And the conclusion:

$VAR1 = {
      'some_table_ibfk_3' => {
                                     'PK_NAME' => undef,
                                     'DEFERABILITY' => undef,
                                     'FKTABLE_CAT' => undef,
                                     'PKTABLE_SCHEM' => 'db',
                                     'UNIQUE_OR_PRIMARY' => undef,
                                     'PKTABLE_CAT' => undef,
                                     'FKTABLE_NAME' => 'some_table',
                                     'FKTABLE_SCHEM' => 'db',
                                     'PKTABLE_NAME' => 'some_other_table',
                                     'FKCOLUMN_NAME' => 'some_other_table_id',
                                     'FK_NAME' => 'some_table_ibfk_3',
                                     'DELETE_RULE' => undef,
                                     'PKCOLUMN_NAME' => 'id',
                                     'KEY_SEQ' => '1',
                                     'UPDATE_RULE' => undef
                                   },
      'user_id_2' => {
                       'PK_NAME' => undef,
                       'DEFERABILITY' => undef,
                       'FKTABLE_CAT' => undef,
                       'PKTABLE_SCHEM' => undef,
                       'UNIQUE_OR_PRIMARY' => undef,
                       'PKTABLE_CAT' => undef,
                       'FKTABLE_NAME' => 'some_table',
                       'FKTABLE_SCHEM' => 'db',
                       'PKTABLE_NAME' => undef,
                       'FKCOLUMN_NAME' => 'some_other_table_id',
                       'FK_NAME' => 'user_id_2',
                       'DELETE_RULE' => undef,
                       'PKCOLUMN_NAME' => undef,
                       'KEY_SEQ' => '2',
                       'UPDATE_RULE' => undef
                     },
      'PRIMARY' => {
                     'PK_NAME' => undef,
                     'DEFERABILITY' => undef,
                     'FKTABLE_CAT' => undef,
                     'PKTABLE_SCHEM' => undef,
                     'UNIQUE_OR_PRIMARY' => undef,
                     'PKTABLE_CAT' => undef,
                     'FKTABLE_NAME' => 'some_table',
                     'FKTABLE_SCHEM' => 'db',
                     'PKTABLE_NAME' => undef,
                     'FKCOLUMN_NAME' => 'id',
                     'FK_NAME' => 'PRIMARY',
                     'DELETE_RULE' => undef,
                     'PKCOLUMN_NAME' => undef,
                     'KEY_SEQ' => '1',
                     'UPDATE_RULE' => undef
                   },
      'some_table_ibfk_1' => {
                                     'PK_NAME' => undef,
                                     'DEFERABILITY' => undef,
                                     'FKTABLE_CAT' => undef,
                                     'PKTABLE_SCHEM' => 'db',
                                     'UNIQUE_OR_PRIMARY' => undef,
                                     'PKTABLE_CAT' => undef,
                                     'FKTABLE_NAME' => 'some_table',
                                     'FKTABLE_SCHEM' => 'db',
                                     'PKTABLE_NAME' => 'user_bk2',
                                     'FKCOLUMN_NAME' => 'user_id',
                                     'FK_NAME' => 'some_table_ibfk_1',
                                     'DELETE_RULE' => undef,
                                     'PKCOLUMN_NAME' => 'id',
                                     'KEY_SEQ' => '1',
                                     'UPDATE_RULE' => undef
                                   },
      'some_table_ibfk_2' => {
                                     'PK_NAME' => undef,
                                     'DEFERABILITY' => undef,
                                     'FKTABLE_CAT' => undef,
                                     'PKTABLE_SCHEM' => 'db',
                                     'UNIQUE_OR_PRIMARY' => undef,
                                     'PKTABLE_CAT' => undef,
                                     'FKTABLE_NAME' => 'some_table',
                                     'FKTABLE_SCHEM' => 'db',
                                     'PKTABLE_NAME' => 'user_bk2',
                                     'FKCOLUMN_NAME' => 'coach_id',
                                     'FK_NAME' => 'some_table_ibfk_2',
                                     'DELETE_RULE' => undef,
                                     'PKCOLUMN_NAME' => 'id',
                                     'KEY_SEQ' => '1',
                                     'UPDATE_RULE' => undef
                                   }
    };

Answer 1

It seems that the mysql driver is not yet supported. Performing a quick check by debugging, it looks like this:

SELECT NULL AS PKTABLE_CAT,
   A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM,
   A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
   A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,
   A.TABLE_CATALOG AS FKTABLE_CAT,
   A.TABLE_SCHEMA AS FKTABLE_SCHEM,
   A.TABLE_NAME AS FKTABLE_NAME,
   A.COLUMN_NAME AS FKCOLUMN_NAME,
   A.ORDINAL_POSITION AS KEY_SEQ,
   NULL AS UPDATE_RULE,
   NULL AS DELETE_RULE,
   A.CONSTRAINT_NAME AS FK_NAME,
   NULL AS PK_NAME,
   NULL AS DEFERABILITY,
   NULL AS UNIQUE_OR_PRIMARY
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A,
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL
   AND A.TABLE_NAME = ? ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION

Note that the UPDATE_RULE and DELETE_RULE columns are set to NULL.