Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    30

    Unanswered: Auto increment Column out of sync

    Hi All,
    I have an auto increment column defined with data in it,the problem I face is I deleted data for some rows in it and realize that the order has been thrown out of sync.1,2,3,4,5 is now 1,3,4,5.Is there a way to reset the order to 1,2,3,4?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by narayanyr View Post
    Is there a way to reset the order to 1,2,3,4?
    yes, but don't even think about it

    the purpose of an auto_increment column is to provide unique values, not consecutive values

    leave the gaps alone
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    I'm not sure why you need to have this in sync? Also you have to consider that if this is a primary key on a table that any other tables using these referential identifiers will also need to be modified.

    To answer your question below you can always drop the field and recreate it as follows:

    Code:
    SET INSERT_ID=1;
    ALTER TABLE table_name DROP COLUMN id;
    ALTER TABLE table_name ADD COLUMN id INT UNSIGNED AUTO_INCREMENT
    PRIMARY KEY FIRST;
    I do remember seeing some code once to get the sequence number of a row in the table and this could be used to update the table too.

    The result of these commands can be seen next:

    Code:
    mysql> select * from test2;
    +------+-------+-------+
    | id   | name  | count |
    +------+-------+-------+
    |    1 | Mike  |     0 | 
    |    2 | Duke  |     2 | 
    |    3 | Smith |     1 | 
    |    4 | Dave  |     6 | 
    |    6 | Rozie |     8 | 
    |    7 | Romeo |     0 | 
    |    8 | Khan  |     1 | 
    +------+-------+-------+
    7 rows in set (0.01 sec)
    mysql> set insert_id = 1;
    Query OK, 0 rows affected (0.00 sec)
    mysql> alter table test2 drop column id;
    Query OK, 7 rows affected (0.06 sec)
    Records: 7  Duplicates: 0  Warnings: 0
    mysql> alter table test2 add column id int unsigned auto_increment primary key first;
    Query OK, 7 rows affected (0.03 sec)
    Records: 7  Duplicates: 0  Warnings: 0
    mysql> select * from test2;
    +----+-------+-------+
    | id | name  | count |
    +----+-------+-------+
    |  1 | Mike  |     0 | 
    |  2 | Duke  |     2 | 
    |  3 | Smith |     1 | 
    |  4 | Dave  |     6 | 
    |  5 | Rozie |     8 | 
    |  6 | Romeo |     0 | 
    |  7 | Khan  |     1 | 
    +----+-------+-------+
    7 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't use an auto number value as a surrogate for a value that has meaning in the outside world

    if you must have sequential numbers then write your own routine to allow for sequential numbers or persuade your beancounters that its not neccessary.. they will disagree.

    autonumbers are for internal use, use within the system to make a value unique, they should not have meaning applied to them outside the system for things like Order, Invoice or GRN's.

    Im certain Ronans a suggestion above works, but that is curing the symptoms NOT the problem.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and of course if there are any foreign keys which reference this auto_increment, then renumbering the values will create an ~unmitigated~ disaster
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2010
    Posts
    30
    Great Thanks guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •