MySQL Auto Increment Based on Foreign Key

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 re开发者_StackOverflow中文版sult 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.

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

If you mean specifically starting at zero, the documentation says auto_increment starts at 1 by design. However, "If the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value."

If you mean to recycle numbers that are no longer used in the table, that should happen automatically, if the removed numbers were the highest number used.