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 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.

Answer 2

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