Column Auto Increase Issue

Posted on 2012-08-22
Last Modified: 2012-08-22
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.
Question by:davidw88
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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"

    Author Comment

    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.
    LVL 142

    Accepted Solution

    wait, wait DB Engine is the table?
    if it's InnoDB, then indeed the "reseqencing" does not occur ...

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now