Incremento automático de MySQL basado en clave externa
Digamos que tengo la siguiente tabla llamada activos con los siguientes campos:
id | job_id | title
Me gustaría usar id y job_id como las claves principales. job_id es la clave foránea. El campo id se incrementa automáticamente. ¿Cómo obtendría id para comenzar a incrementar en 0 si no hay una fila con el mismo job_id? Si hay una fila con el mismo job_id, ¿aumenta la identificación en 1 y así sucesivamente?
El resultado que estoy buscando es una tabla que se vea así:
id | job_id | title
0 1 hi
1 1 hello
2 1 goodbye
0 2 hi
1 2 hello
Ahora digamos que se agrega una nueva fila con job_id = 3. El campo id debería comenzar a incrementarse automáticamente desde 0 nuevamente.