[Dbix-class] has_many/belongs_to relationship to non-primary key

[Matija Grabnar]

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