Incremento automático do MySQL baseado em chave estrangeira
Digamos que eu tenha a seguinte tabela chamada de ativos com os seguintes campos:
id | job_id | title
Eu gostaria de usar id e job_id como chaves primárias. job_id é a chave estrangeira. O campo id é incrementado automaticamente. Como eu obteria o id para começar a aumentar em 0 se não houver nenhuma linha com o mesmo job_id. Se houver uma linha com o mesmo job_id, aumente o ID em 1 e assim por diante?
O resultado que estou procurando é uma tabela que se parece com isso:
id | job_id | title
0 1 hi
1 1 hello
2 1 goodbye
0 2 hi
1 2 hello
Agora, digamos que uma nova linha com job_id = 3 seja adicionada. O campo id deve iniciar o incremento automático de 0 novamente.