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.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
wait, wait DB Engine is the table?
if it's InnoDB, then indeed the "reseqencing" does not occur ...
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.