| Subject: | Deploying schema with multi-column foreign key fails with SQLite-3.7.5 |
With a schema file like:
package Bett::Schema::Result::Question
__PACKAGE__->table("question");
__PACKAGE__->add_columns(
"player",
{ data_type => "text", is_nullable => 0 },
"lexed",
{ data_type => "text", is_nullable => 0 },
"league",
{ data_type => "text", is_nullable => 0 },
"exercise",
{ data_type => "text", is_nullable => 0 },
"course",
{ data_type => "text", is_nullable => 0 },
"quoted",
{ data_type => "text", is_nullable => 0 },
"grammatical",
{ data_type => "bool", is_nullable => 0 },
);
__PACKAGE__->set_primary_key("lexed", "exercise", "league");
__PACKAGE__->belongs_to(
"played_by",
"Bett::Schema::Result::Member",
{ "foreign.player" => "self.player",
"foreign.league" => "self.league"
},
{
is_deferrable => 0,
join_type => "LEFT",
on_delete => "CASCADE",
on_update => "CASCADE",
},
);
And the Member schema:
package Bett::Schema::Result::Member;
__PACKAGE__->table("member");
__PACKAGE__->add_columns(
"league",
{ data_type => "text", is_foreign_key => 1, is_nullable => 0 },
"player",
{ data_type => "text", is_foreign_key => 1, is_nullable => 0 },
);
__PACKAGE__->set_primary_key("player", "league");
__PACKAGE__->has_many(
"questions",
"Bett::Schema::Result::Question",
{ "foreign.player" => "self.player",
"foreign.league" => "self.league"
},
{ cascade_copy => 0, cascade_delete => 0 },
);
Deploying this, sqlite3 shows the question table schema:
Show quoted text
sqlite> .schema question
CREATE TABLE question (
player text NOT NULL,
lexed text NOT NULL,
league text NOT NULL,
exercise text NOT NULL,
course text NOT NULL,
quoted text NOT NULL,
grammatical bool NOT NULL,
PRIMARY KEY (lexed, exercise, league),
FOREIGN KEY(league) REFERENCES member(league)
);
CREATE INDEX question_idx_league_player ON question (league, player);
Show quoted textsqlite>
What happened to the other column, player in the foreign key?
[greg@momotaro bett]$ re.pl
$ use lib 'lib'
$ use Bett::Schema
$ use Bett::Model::DB
$ my $s = Bett::Schema->connect( Bett::Model::DB->config->{connect_info} )
my
($f_class,$f_class_eclipse_1,$f_class_eclipse_2,$f_class_eclipse_3,$f_class_eclipse_4,$f_class_eclipse_5,$f_class_eclipse_6,$rel,$rel_eclipse_1,$rel_eclipse_2,$rel_eclipse_3,$rel_eclipse_4,$rel_eclipse_5,$rel_eclipse_6);
$f_class = 'Bett::Schema::Result::Genre';
$f_class_eclipse_1 = 'Bett::Schema::Result::League';
$f_class_eclipse_2 = 'Bett::Schema::Result::League';
$f_class_eclipse_3 = 'Bett::Schema::Result::Player';
$f_class_eclipse_4 = 'Bett::Schema::Result::Member';
$f_class_eclipse_5 = 'Bett::Schema::Result::Player';
$f_class_eclipse_6 = 'Bett::Schema::Result::Role';
$rel = 'genre';
$rel_eclipse_1 = 'league';
$rel_eclipse_2 = 'league';
$rel_eclipse_3 = 'player';
$rel_eclipse_4 = 'player';
$rel_eclipse_5 = 'player';
$rel_eclipse_6 = 'role';
$Bett_Schema1 = Bett::Schema=HASH(0xb260f68);
$ $s->resultset('Question')->create({player=>'V9741065',lexed=>'did
rebia die',league=>'GL00005',exercise=>'clay',course=>'YN',quoted=>'Did
Rebia die?',grammatical=>1})
Runtime error: DBIx::Class::ResultSet::create(): DBI Exception:
DBD::SQLite::db prepare_cached failed: foreign key mismatch [for
Statement "INSERT INTO question ( course, exercise, grammatical, league,
lexed, player, quoted) VALUES ( ?, ?, ?, ?, ?, ?, ? )"] at (eval 1337)
line 6
What foreign key constraint could I be violating here?
Isn't it only the one on Member? But,
$s->resultset('Member')->find({player=>'V9741065',league=>'GL00005'})->id
$VAR1 = 'V9741065';
$VAR2 = 'GL00005';