How do you create foreign keys in attached sqlite databases?

Question

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.

I have setup the classes in DBIx::Class, using $schema->storage->dbh_do to attach the database with the schema, and using $schema-deploy() to create the database.

However when it comes to creating the foreign key on the second table I get the following error:

DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI Exception: DBD::SQLite::db do failed: near ".": syntax error 

Taking away DBIx::Class the simplest test to reproduce is as follows.

sqlite3 initial.db
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"   
sqlite> attach database 'other.db' as 'other';
sqlite> create table other.a( col1_a, col2_a);
sqlite> create table other.b( col1_b, col2_b, foreign key(col1_b) references other.a(col1_a));
Error: near ".": syntax error
sqlite> create table other.b( col1_b, col2_b, foreign key(col1_b) references a(col1_a));
sqlite> 

Removing the schema from the foreign key clause will successfully create the table.

How can i create the tables in the foreign schema with DBIx::Class?

EDIT: A full sample of the code.

package MyApp::Schema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_namespaces();
1;


package MyApp::Schema::Result::A;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('other_db.A');
__PACKAGE__->add_columns(qw/ a1 a2 /);
__PACKAGE__->set_primary_key('a1');
__PACKAGE__->has_many(bs => 'MyApp::Schema::Result::B', 'b1');
1;

package MyApp::Schema::Result::B;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('other_db.B');
__PACKAGE__->add_columns(qw/ b1 b2 /);
__PACKAGE__->set_primary_key('b1');
__PACKAGE__->belongs_to(a => 'MyApp::Schema::Result::A', 'b1');
1;

The main script:

use MyApp::Schema;

my $schema = MyApp::Schema->connect('dbi:SQLite:dbname=test.db','','',{});

my $res = $schema->storage->dbh_do(
    sub {
        my ($storage, $dbh) = @_;
        $dbh->do("attach database 'other.db' as other_db");
    }
);

$schema->deploy();

The error given is:

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

Livonia

So you'd prefer to use the "other." in to keep your code cleaner/simpler? – Pelham

I really have no preference. My preference is that the DBIx::Class::Schema::deploy() generates a create table statement that will work on SQLite. – Livonia

Can you post your the perl code since it seems to me that your problem is with perl, not sqlite's syntax. – Pelham

Answer 1

Simple answer is that you can't (and don't need to) specify the database name when with the table name in foreign key definitions. The sqlite docs indicates it as such.

This link shows how you can use the database name with the new table name in the "create table db.table" part:

http://www.sqlite.org/lang_createtable.html

Whereas this link shows how you can't use the database name in the foreign key definition:

http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause

Pelham

But what if the attached database has the same table name as the main database? How would you specify a foreign key relationship which resolves that ambiguity? – Ritzy