auto_increment on multiple columns?

[Jimi]

Hi

I have been using MySQL for quite some time now, but only doing the very basic things. But now there is a thing I want to do that I haven't been able to figure out how to solve.

I want a table "categories" with the following columns:
-categoryId, a unique int value, auto incremented
-userId, int
-name, varchar
-categoryIndex, int auto incremented

But the thing is that I want the categoryIndex to be autoincremented based on the userId. That is, for each userId the categoryIndex should start from 1.

Like this:
categoryId__userId___name____categoryIndex
------------------------------------------------------
1___________1______Home______1
2___________1______Work______2
3___________1______Vacation____3
4___________2______Animals____1
5___________2______Plants______2
6___________3______Funny______1
7___________3______Computers__2

So if do:

INSERT INTO categories (userId, name) VALUES (2, 'Test');

the new row should have categoryId 8 and categoryIndex 3. And if I then do:

INSERT INTO categories (userId, name) VALUES (4, 'Test2');

the new row should have categoryId 9 and categoryIndex 1.

Is this possible to do? I use MySQL 4.0.24-nt.

At the moment I have worked around the problem by before each insert first finding out what the highest categoryIndex value is for that particular userId, and then add 1.

Regards
/Jimi