views:

147

answers:

3

Is there a way with MySQL (5.0 specifically) to have an auto_increment field who's value is based on a grouping column?

Example:

id  name   group_field
1   test   1
2   test2  1
1   test3  2
2   test4  2
1   test5  3
2   test6  3

I'd like to not have to go through any 'crazy' methods to achive this, but will if necessary.

+5  A: 

For MyISAM and BDB tables you can have an auto_increment field as a secondary part of key, e.g.

CREATE TABLE foo (
   id          INT AUTO_INCREMENT NOT NULL,
   group_field INT NOT NULL,
   name        VARCHAR(128),

   PRIMARY KEY(group_field, id)
);

Here's what the manual says about this

In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

Paul Dixon
does this work with innodb tables as well?
gms8994
As the manual only mentions MyISAM and BDB, I would guess not. See also http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html
Paul Dixon
A: 

Have you thought about composite primary keys?

karim79
A: 

You can achieve that with trigger on insert, setting max(id) from table group by group_field having group_fileld = @inserted_group;

vartec