There seems to be an additional problem, which lies in the MySQL producer
itself (maybe others too). Keeping it in the thread as it is from the same
department. Example (a crude linked list):
__PACKAGE__->add_columns (
id => {
data_type => 'BIGINT',
is_auto_increment => 1,
},
prev_id => {
data_type => 'BIGINT',
is_foreign_key => 1,
},
name => {
data_type => 'VARCHAR',
size => 255,
},
);
__PACKAGE__->set_primary_key ('id');
__PACKAGE__->add_unique_constraint (prev => [qw/prev_id/]); # enforces 1:1
__PACKAGE__->might_have ('next_option', __PACKAGE__, 'prev_id' );
__PACKAGE__->belongs_to ('prev_option', __PACKAGE__, 'prev_id', {join_type =>
'left' });
When deploying this SQLT spits out:
CREATE TABLE `options` (
`id` BIGINT NOT NULL auto_increment,
`prev_id` BIGINT NOT NULL,
`name` VARCHAR(255) NOT NULL,
INDEX (`prev_id`),
PRIMARY KEY (`id`),
UNIQUE `prev` (`prev_id`),
CONSTRAINT `options_fk_prev_id` FOREIGN KEY (`prev_id`) REFERENCES
`options` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
Which results in double indexing of prev_id:
mysql> show index from options;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| options | 0 | PRIMARY | 1 | id | A |
0 | NULL | NULL | | BTREE | |
| options | 0 | prev | 1 | prev_id | A |
0 | NULL | NULL | | BTREE | |
| options | 1 | prev_id | 1 | prev_id | A |
0 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
