MySQL auto increment with sub index

Asked Apr 12 '17 at 14:58

Active Apr 12 '17 at 15:51

Viewed 101 times

I have two MySQL tables, jobs and job-parts for which there are several job parts for each job referenced back using a job_id column.

The jobs table has the normal auto-increment on the id column which I'm using as the job number. So currently I pull the job parts out like follows:

SELECT jobs.id, job_parts.id FROM jobs,job_parts WHERE job_parts.job_id = jobs.id;

What I need to do is set up some sort 'auto increment' on the job_parts table, but one that resets for each different id on the first table, resulting in combined job references that look like

1.1, 1.2, 1.3,

2.1, 2.2, 2.3, 2.4

3.1, 3.2 ... and so forth.

Ideally I'd like to be able to delete parts from the second table and the part id column to retain the numbers that were originally assigned when the parts were inserted, just like auto increment does normally.

Anyone know a nice clean way to do this with a SQL statement?

edited Apr 12 '17 at 15:51

Paulie-C

1,674
1
13
29

asked Apr 12 '17 at 14:58

Darren Crabb

This question has already been asked and answered here on SO. Either use triggers with innodb table engine, or you need to use myisam. But it is better if you do not do it at all. – Shadow Apr 12 '17 at 15:06
@Shadow - Thanks, are you able to point me at the previously asked question? I did quite a few searches with the stuff I found not quite being the same situation. – Darren Crabb Apr 12 '17 at 15:08
Brilliant. Thanks Shadow. – Darren Crabb Apr 12 '17 at 15:11
Here is one for myisam: http://stackoverflow.com/questions/677542/auto-increment-by-group – Shadow Apr 12 '17 at 15:12
Don't forget to read the (not-accepted) answer to the duplicate that says: "Don't". – Johan Apr 12 '17 at 15:13
If you search for "MySQL autoincrement per group", then you will find many more. – Shadow Apr 12 '17 at 15:13
As always, half the problem is knowing what to search for! – Darren Crabb Apr 12 '17 at 15:14

MySQL auto increment with sub index

0 Answers0