Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

ChrisDolan (2855)

ChrisDolan
  (email not shown publicly)
http://www.chrisdolan.net/

Journal of ChrisDolan (2855)

Tuesday December 05, 2006
02:38 PM

MySQL foreign key syntax

[ #31813 ]

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!

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.