• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Column Auto Increase Issue

Hi experts,

Assume that there is a table "processors" and has a column "processorId". "processorId" is PK, mediumInt, not null and auto inc starting from 1.

Here is a scenario. Assume that there are five rows in the table. "processorId" is from 1 to 5. now that I delete the row with "proccessorId = 5",  there are four rows left. If I insert a new record,  this new row's "processorId" will be 6. now the current processorIds become 1, 2, 3, 4, 6.  How to make the processorId to increase continually without any gaps? (i.e. 1, 2, 3, 4, 5, 6, 7, etc).

Any ideas? thanks so much.
  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, in Mysql, if you delete the row(s) with the highest value, it will be reused with the next insert(s).

now, you should not UPDATE a Primary key value at all, just for a "sequencing" issue ...
I recommend you do NOT try to implement this "update"
davidw88Author Commented:
Thanks so much Angel.

This is why I am puzzled. I deleted a row with the highest PK value and then inserted a new one. Why did not I get "sequencing" value increment for PK? Did I miss anything?

Thanks again.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
wait, wait DB Engine is the table?
if it's InnoDB, then indeed the "reseqencing" does not occur ...

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now