[Dbix-class] Another bug in the SQLT parser

[Peter Rabbitson]

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      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+