title: How do you create foreign keys in attached sqlite databases?
body: <p>I am trying to create a sqlite3 database as a test environment that mimics the production environment.  Due to the way production is setup the tables are in multiple schemas.</p>
  
  <p>I have setup the classes in DBIx::Class, using <code>$schema-&gt;storage-&gt;dbh_do</code> to attach the database with the schema, and using <code>$schema-deploy()</code> to create the database.</p>
  
  <p>However when it comes to creating the foreign key on the second table I get the following error:</p>
  
  <pre><code>DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI Exception: DBD::SQLite::db do failed: near ".": syntax error 
  </code></pre>
  
  <p>Taking away <code>DBIx::Class</code> the simplest test to reproduce is as follows.</p>
  
  <pre><code>sqlite3 initial.db
  SQLite version 3.6.23
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"   
  sqlite&gt; attach database 'other.db' as 'other';
  sqlite&gt; create table other.a( col1_a, col2_a);
  sqlite&gt; create table other.b( col1_b, col2_b, foreign key(col1_b) references other.a(col1_a));
  Error: near ".": syntax error
  sqlite&gt; create table other.b( col1_b, col2_b, foreign key(col1_b) references a(col1_a));
  sqlite&gt; 
  </code></pre>
  
  <p>Removing the schema from the foreign key clause will successfully create the table.</p>
  
  <p>How can i create the tables in the foreign schema with DBIx::Class?</p>
  
  <p>EDIT: A full sample of the code.</p>
  
  <pre><code>package MyApp::Schema;
  use base qw/DBIx::Class::Schema/;
  __PACKAGE__-&gt;load_namespaces();
  1;
  
  
  package MyApp::Schema::Result::A;
  use base qw/DBIx::Class::Core/;
  __PACKAGE__-&gt;table('other_db.A');
  __PACKAGE__-&gt;add_columns(qw/ a1 a2 /);
  __PACKAGE__-&gt;set_primary_key('a1');
  __PACKAGE__-&gt;has_many(bs =&gt; 'MyApp::Schema::Result::B', 'b1');
  1;
  
  package MyApp::Schema::Result::B;
  use base qw/DBIx::Class::Core/;
  __PACKAGE__-&gt;table('other_db.B');
  __PACKAGE__-&gt;add_columns(qw/ b1 b2 /);
  __PACKAGE__-&gt;set_primary_key('b1');
  __PACKAGE__-&gt;belongs_to(a =&gt; 'MyApp::Schema::Result::A', 'b1');
  1;
  </code></pre>
  
  <p>The main script:</p>
  
  <pre><code>use MyApp::Schema;
  
  my $schema = MyApp::Schema-&gt;connect('dbi:SQLite:dbname=test.db','','',{});
  
  my $res = $schema-&gt;storage-&gt;dbh_do(
      sub {
          my ($storage, $dbh) = @_;
          $dbh-&gt;do("attach database 'other.db' as other_db");
      }
  );
  
  $schema-&gt;deploy();
  </code></pre>
  
  <p>The error given is:</p>
  
  <pre><code>DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI Exception: DBD::SQLite::db do failed: near ".": syntax error [for Statement "CREATE TABLE other_db.B (
    b1  NOT NULL,
    b2  NOT NULL,
    PRIMARY KEY (b1),
    FOREIGN KEY(b1) REFERENCES other_db.A(a1)
  )"] at dbi.pl line 17
   (running "CREATE TABLE other_db.B (
    b1  NOT NULL,
    b2  NOT NULL,
    PRIMARY KEY (b1),
    FOREIGN KEY(b1) REFERENCES other_db.A(a1)
  )") at dbi.pl line 17
  </code></pre>