Is this something that database theory makes impossible (and I just
didn't know it) or is this a bug in DBIx::Class?
Summary: Say I have two Classes, in a belongs_to / has_many
relationship, but with one important difference: the belongs_to
relationship does not go to the primary key of the other table, but to
another key. When I try to do that, I get an extra constraint that
prevents me from inserting anything into the table.
package HSL::MailStructure::A;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("a");
__PACKAGE__->add_columns(
"i",
{ data_type => "INT", default_value => 0, is_nullable => 0, size => 11 },
"j",
{ data_type => "INT", default_value => 0, is_nullable => 1, size => 11 },
);
__PACKAGE__->set_primary_key("i");
__PACKAGE__->add_unique_constraint("a_j", ["j"]);
__PACKAGE__->has_many(
"bs",
"HSL::MailStructure::B",
{ "foreign.j" => "self.j" },
);
1;
and
package HSL::MailStructure::B;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("b");
__PACKAGE__->add_columns(
"id",
{ data_type => "INT", default_value => 0, is_nullable => 0, size => 11 },
"j",
{ data_type => "INT", default_value => 0, is_nullable => 1, size => 11 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->belongs_to("a","HSL::MailStructure::A",
{"foreign.j"=>"self.j"});
1;
It works if table B refers to the primary key of table A, but not if it
refers
to another column:
If the key that class B is tied to is "i" (the primary key of table a),
then sql translator generates a single constraint statement, (in table
b) that looks like this:
CREATE TABLE `b` (
`id` int(11) NOT NULL default '0',
`j` int(11) default '0',
PRIMARY KEY (`id`),
KEY `id` (`id`),
KEY `j` (`j`),
CONSTRAINT `b_fk_j` FOREIGN KEY (`j`) REFERENCES `a` (`i`) ON DELETE
CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
table a is created without foreign key constraints:
CREATE TABLE `a` (
`i` int(11) NOT NULL default '0',
`j` int(11) default '0',
PRIMARY KEY (`i`),
UNIQUE KEY `a_j` (`j`),
KEY `i` (`i`),
KEY `j` (`j`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
However, if class B refers to the non-primary key, then table a gets
an extra foreign key constraint:
CREATE TABLE `a` (
`i` int(11) NOT NULL default '0',
`j` int(11) default '0',
PRIMARY KEY (`i`),
UNIQUE KEY `a_j` (`j`),
KEY `i` (`i`),
KEY `j` (`j`),
CONSTRAINT `a_fk_j` FOREIGN KEY (`j`) REFERENCES `b` (`j`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
That constraint means that it is impossible to insert anything into
table a, since the constraint means it would need to have the
appropriate value already present in b - but you can't insert it in b,
because that has it's own constraint preventing that:
mysql> insert into a values (1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`hsl/a`, CONSTRAINT `a_fk_j` FOREIGN KEY (`j`)
REFERENCES `b` (`j`))
mysql> insert into b values (1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`hsl/b`, CONSTRAINT `b_fk_j` FOREIGN KEY (`j`)
REFERENCES `a` (`j`) ON DELETE CASCADE ON UPDATE CASCADE)
Is what I am trying to do impossible due to something in database theory
that I am overlooking (a distinct possibility - if so, please educate
me), or is it a bug in deploy and
SQL::Translator::Producer::DBIx::Class::File?
The reason I need to tie the relationship to the non-primary key is that
it is not the only relationship: there are other relationships tied to
the primary key of the real table I'm having this problem with, this is
just as small a reduction of the problem as I can manage...
Best regards,
Matija Grabnar
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
