如何在附加的 sqlite 数据库中创建外键? [英] How do you create foreign keys in attached sqlite databases?

问题描述

我正在尝试创建一个 sqlite3 数据库作为模拟生产环境的测试环境.由于生产设置的方式，表处于多个模式中.

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.

我已经在 DBIx::Class 中设置了类，使用 $schema->storage->dbh_do 将数据库与模式连接起来，并使用 $schema-deploy() 来创建数据库.

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 

去掉 DBIx::Class 最简单的重现测试如下.

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.

如何使用 DBIx::Class 在外部模式中创建表?

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

完整的代码示例.

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;

主脚本:

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();

给出的错误是:

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

推荐答案

简单的答案是，当在外键定义中使用表名时，您不能(也不需要)指定数据库名.sqlite 文档说明了这一点.

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.

此链接显示了如何在创建表 db.table"部分中使用带有新表名的数据库名:

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-关键从句

这篇关于如何在附加的 sqlite 数据库中创建外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！