Do I need to manually create indexes for a DBIx::Class belongs_to relationship
- by Dancrumb
I'm using the DBIx::Class modules for an ORM approach to an application I have.
I'm having some problems with my relationships.
I have the following
package MySchema::Result::ClusterIP;
use strict;
use warnings;
use base qw/DBIx::Class::Core/;
our $VERSION = '1.0';
__PACKAGE__->load_components(qw/InflateColumn::Object::Enum Core/);
__PACKAGE__->table('cluster_ip');
__PACKAGE__->add_columns( # Columns here );
__PACKAGE__->set_primary_key('objkey');
__PACKAGE__->belongs_to( 'configuration' => 'MySchema::Result::Configuration', 'config_key');
__PACKAGE__->belongs_to( 'cluster' => 'MySchema::Result::Cluster',
{ 'foreign.config_key' => 'self.config_key',
'foreign.id' => 'self.cluster_id'
}
);
As well as
package MySchema::Result::Cluster;
use strict;
use warnings;
use base qw/DBIx::Class::Core/;
our $VERSION = '1.0';
__PACKAGE__->load_components(qw/InflateColumn::Object::Enum Core/);
__PACKAGE__->table('cluster');
__PACKAGE__->add_columns( # Columns here );
__PACKAGE__->set_primary_key('objkey');
__PACKAGE__->belongs_to( 'configuration' => 'MySchema::Result::Configuration', 'config_key');
__PACKAGE__->has_many('cluster_ip' => 'MySchema::Result::ClusterIP',
{ 'foreign.config_key' => 'self.config_key',
'foreign.cluster_id' => 'self.id'
});
There are a couple of other modules, but I don't believe that they are relevant.
When I attempt to deploy this schema, I get the following error:
DBIx::Class::Schema::deploy(): DBI Exception: DBD::mysql::db do failed: Can't create table 'test.cluster_ip' (errno: 150) [
for Statement "CREATE TABLE `cluster_ip` (
`objkey` smallint(5) unsigned NOT NULL auto_increment,
`config_key` smallint(5) unsigned NOT NULL,
`cluster_id` char(16) NOT NULL,
INDEX `cluster_ip_idx_config_key_cluster_id` (`config_key`, `cluster_id`),
INDEX `cluster_ip_idx_config_key` (`config_key`),
PRIMARY KEY (`objkey`),
CONSTRAINT `cluster_ip_fk_config_key_cluster_id` FOREIGN KEY (`config_key`, `cluster_id`) REFERENCES `cluster` (`config_key`, `id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `cluster_ip_fk_config_key` FOREIGN KEY (`config_key`) REFERENCES `configuration` (`config_key`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB"] at test_deploy.pl line 18
(running "CREATE TABLE `cluster_ip` (
`objkey` smallint(5) unsigned NOT NULL auto_increment,
`config_key` smallint(5) unsigned NOT NULL,
`cluster_id` char(16) NOT NULL,
INDEX `cluster_ip_idx_config_key_cluster_id` (`config_key`, `cluster_id`),
INDEX `cluster_ip_idx_config_key` (`config_key`),
PRIMARY KEY (`objkey`),
CONSTRAINT `cluster_ip_fk_config_key_cluster_id` FOREIGN KEY (`config_key`, `cluster_id`) REFERENC
ES `cluster` (`config_key`, `id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `cluster_ip_fk_config_key` FOREIGN KEY (`config_key`) REFERENCES `configuration` (`conf
ig_key`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB") at test_deploy.pl line 18
From what I can tell, MySQL is complaining about the FOREIGN KEY constraint, in particular, the REFERENCE to (config_key, id) in the cluster table. From my reading of the MySQL documentation, this seems like a reasonable complaint, especially in regards to the third bullet point on this doc page.
Here's my question. Am I missing something in the DBIx::Class module? I realize that I could explicitly create the necessary index to match up with this foreign key constraint, but that seems to be repetitive work. Is there something I should be doing to make this occur implicitly?
