Table with multiple incrementing columns which doesn't reuse deleted column values

Question

I wish to have a MySQL InnoDB table with an auto-incrementing primary key which also has a column which increments independently based on the type of record.

For instance, my records might be as shown below (shown as two tables to highlight the two incrementing columns).

If a record at the end of the sub-incrementing series is deleted such as PK 12, and then a record is inserted for that same type, the SecondaryKey should not be duplicated, and the new record should have PK 17 and SecondaryKey 5 (and not 4).

How is this best accomplished?

+----+------+--------------+----------------+
| PK | Type | SecondaryKey |      Data      |
+----+------+--------------+----------------+
|  1 | a    |            1 | Data for PK 1  |
|  2 | a    |            2 | Data for PK 2  |
|  3 | c    |            1 | Data for PK 3  |
|  4 | d    |            1 | Data for PK 4  |
|  5 | a    |            3 | Data for PK 5  |
|  6 | b    |            1 | Data for PK 6  |
|  7 | c    |            2 | Data for PK 7  |
|  8 | a    |            4 | Data for PK 8  |
|  9 | d    |            2 | Data for PK 9  |
| 10 | b    |            2 | Data for PK 10 |
| 11 | d    |            3 | Data for PK 11 |
| 12 | d    |            4 | Data for PK 12 |
| 13 | a    |            5 | Data for PK 13 |
| 14 | b    |            3 | Data for PK 14 |
| 15 | b    |            4 | Data for PK 15 |
| 16 | c    |            3 | Data for PK 16 |
+----+------+--------------+----------------+

+----+------+--------------+----------------+
| PK | Type | SecondaryKey |      Data      |
+----+------+--------------+----------------+
|  1 | a    |            1 | Data for PK 1  |
|  2 | a    |            2 | Data for PK 2  |
|  5 | a    |            3 | Data for PK 5  |
|  8 | a    |            4 | Data for PK 8  |
| 13 | a    |            5 | Data for PK 13 |
|  6 | b    |            1 | Data for PK 6  |
| 10 | b    |            2 | Data for PK 10 |
| 14 | b    |            3 | Data for PK 14 |
| 15 | b    |            4 | Data for PK 15 |
|  3 | c    |            1 | Data for PK 3  |
|  7 | c    |            2 | Data for PK 7  |
| 16 | c    |            3 | Data for PK 16 |
|  4 | d    |            1 | Data for PK 4  |
|  9 | d    |            2 | Data for PK 9  |
| 11 | d    |            3 | Data for PK 11 |
| 12 | d    |            4 | Data for PK 12 |
+----+------+--------------+----------------+

What if you ever want to delete a row. Should the sequence for that type be adjusted? — Paul Spiegel, Feb 17 '17 at 20:31
You can do this with [triggers](https://dev.mysql.com/doc/refman/5.7/en/triggers.html). — A C, Feb 17 '17 at 20:49
@PaulSpiegel As described above, don't wish to reuse the ID. — user1032531, Feb 17 '17 at 21:38
@Barmar I've used MyISAM to do so for composite autoincrementing PKs, and then used the application to handle, but I would rather not. — user1032531, Feb 17 '17 at 21:39
@sumit A given user group is responsible for each type, and I want them to have their own incrementing series. — user1032531, Feb 17 '17 at 21:39
@AC How can it be done with triggers? What about the part about not duplicating a previously deleted sub-sequence ID? — user1032531, Feb 17 '17 at 21:40
I don't believe the other question addresses not reusing deleted sub-incrementing keys. — user1032531, Feb 17 '17 at 21:42
If your sequence my have gaps, then why don't you just use one sequence for all types? — Paul Spiegel, Feb 17 '17 at 21:44
OP is right - The linked answer doesn't address the requirement for not reusing a number from a deleted row. So i've voted for reopening. — Paul Spiegel, Feb 17 '17 at 22:03
@PaulSpiegel Thanks for voting to reopen. The reason I don't want one sequence for all types is doing gives other users knowledge about another user's sequences, and also does not seem as personal. — user1032531, Feb 18 '17 at 01:18
Create a table that will store last AI numbers per type. Use a trigger to increment it on every insert and copy to the original table. — Paul Spiegel, Feb 18 '17 at 02:12
@PaulSpiegel Thanks Paul, Yeah, that should work. Now, how to implement it... — user1032531, Feb 18 '17 at 16:10
@PaulSpiegel Actually, it will not work. MySQL doesn't allow to actions on the same table. — user1032531, Feb 19 '17 at 16:56
@PaulSpiegel See http://stackoverflow.com/questions/42330198/trigger-to-update-and-select-on-same-table-error-1235-42000 for why it will not work. — user1032531, Feb 19 '17 at 17:10
@user1032531 I have [answered](http://stackoverflow.com/a/42333142/5563083) your question. — Paul Spiegel, Feb 19 '17 at 21:34

Table with multiple incrementing columns which doesn't reuse deleted column values

0 Answers0

Linked