auto increment field on a secondary column in a multiple-column index

[Hubert Retif]

Hi,

I am using MySql 4.1.10. Considering the structure of the following table:

CREATE TABLE `b_answers` (
`id_answers` int(11) NOT NULL default '0',
`id_questions` int(11) NOT NULL default '0',
`fname_content` varchar(90) default NULL,
`id_content_type` varchar(5) default NULL,
`answer_right` char(1) NOT NULL default '',
`active` int(11) default NULL,
`sequence_nb` int(11) NOT NULL,
`answer` text,
PRIMARY KEY (`id_answers`,`id_questions`,`sequence_nb`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I try to set an auto increment beginning with 0 on field sequence_nb.

I have already data in this table where sequence_nb is correctly filled in (beginning with 0).

If I set auto increment on sequence_nb, all existing records with sequence_nb=0 are overwritten with value 1.

If I set default '0' and auto increment on sequence_nb, it seems not to be allowed (error message).

Does anyone know whether I have a chance to get it as I want?

Thanks,
Hubert