MySQL Auto Increment Based on Foreign Key

Question

Lets say I have the following table called assets with the following fields:

id | job_id | title

I would like to use id and job_id as the primary keys. job_id is the foreign key. The id field is auto incremented. How would I get id to start incrementing at 0 if there is no row with the same job_id. If there is a row with the same job_id then increment id by 1 and so on?

The result I am looking for is a table that looks like this:

id | job_id | title
0     1        hi
1     1        hello
2     1        goodbye
0     2        hi
1     2        hello

Now lets say a new row with job_id = 3 is added. The id field should start auto incrementing from 0 again.

Answer 1

If you use the MyISAM storage engine, the auto-increment column in a multi-column primary key starts over with each new value in the non-auto-increment column.

See http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html