MySQL foreign key syntax

ChrisDolan on 2006-12-05T19:38:28

I learned today on #dbix-class that MySQL supports foreign key definitions in create table statements, but not inline declarations. That is, MySQL understands this syntax:

create table book (
  id int,
  author_id int,
  FOREIGN KEY fk_author_id (author_id) REFERENCES author (id)
) TYPE=InnoDB;

but not this nicer syntax (it silently ignores the "references" clause):

create table book (
  id int,
  author_id int references author (id),
) TYPE=InnoDB;

Perl to the rescue! I can write my schema in the latter syntax and use SQL::Translator to rewrite into the supported syntax.

This short program will perform the translation.

use SQL::Translator;
my $infile = shift || die "Syntax: $0 schema.sql > schema_mysql.sql\n";
my $translator = SQL::Translator->new(
   from => 'MySQL',
   to   => 'MySQL',
   add_drop_table => 1);
print $translator->translate(filename => $infile);

Credit goes to Brandon Black (blblack) for the detailed advice!