1

I'm sure I used to be able to do this (15+ years ago), but I don't remember how, or know if it's still possible.

I have a table with a composite primary key - MainID and AutoIncrementID. The AutoIncrementID is set to auto increment.

If I insert three entries with a MainID of 1, the AutoIncrementID will be 1, 2, and 3 respectively.

If I then insert two entries with a MainID of 2, I would like the AutoIncrementID to be 1, then 2.

If I then insert another entry with a MainID of 1, I would like the AutoIncrementID to be 4

It doesn't work this way by default in MySQL default, is it possible to do, and how do I configure it? All my searches are flooded with general auto increment information.

This question is for both MySQL 5.7 and MySQL 8.0.

Thanks!

Ben Holness
  • 183
  • 9

1 Answers1

1

That is only possible with MyISAM storage engine

See my old post How can you have two auto-incremental columns in one table?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536