Description:
I don't know if it is a bug or if the following behavior is wanted.
when having myisam, i can have a primary index, including 2 colums
and an autoindex on the second colum of the primary
when having innodb, the autoindex have to be on the first cloum of the primary
I Have this tables:
CREATE TABLE `test` (
`set_id` smallint(5) unsigned NOT NULL default '0',
`id` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`set_id`,`id`)
) ENGINE=myisam DEFAULT CHARSET=latin1
[it works]
CREATE TABLE `test` (
`set_id` smallint(5) unsigned NOT NULL default '0',
`id` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`set_id`,`id`)
) ENGINE=innodb DEFAULT CHARSET=latin1
[it does not work]
CREATE TABLE `test` (
`set_id` smallint(5) unsigned NOT NULL default '0',
`id` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`,`set_id`)
) ENGINE=innodb DEFAULT CHARSET=latin1
[it works]
How to repeat:
CREATE TABLE `test` (
`set_id` smallint(5) unsigned NOT NULL default '0',
`id` tinyint(4) unsigned NOT NULL auto_increment,
PRIMARY KEY (`set_id`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE `test` TYPE = INNODB
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
CREATE TABLE `test` (
`set_id` smallint( 5 ) unsigned NOT NULL default '0',
`id` tinyint( 4 ) unsigned NOT NULL AUTO_INCREMENT ,
PRIMARY KEY ( `set_id` , `id` )
) ENGINE = innodb DEFAULT CHARSET = latin1
doestn't work too
CREATE TABLE `test` (
`set_id` smallint(5) unsigned NOT NULL default '0',
`id` tinyint(4) unsigned NOT NULL,
PRIMARY KEY (`set_id`,`id`)
) ENGINE=innodb DEFAULT CHARSET=latin1
works
