MySQL: restart Autoincrement on a non-key field

[sasankasekhar]

Dear friends,
This is my table:

CREATE TABLE IF NOT EXISTS `exam_student` (
  `exam_roll_no` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `enrol_id` int(10) unsigned NOT NULL,
  `exam_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`enrol_id`,`exam_id`),
  KEY `exam_roll_no` (`exam_roll_no`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

I am trying to get the field exam_roll_no reset to '1' whenever there is a new exam_id
Is it possible? If so, how?

Actually I am trying to assign roll numbers (exam_roll_no) to candidates (with a unique enrol_id) so that each candidate may or may not have the same roll number for different exams.

Thanks in advance.

[pritaeas]

Not quite sure what you are trying to achieve, but it is certainly not possible with an AUTO_INCREMENT column. It is meant to be unique. Can you show some sample data, perhaps that will explain it.