Auto increment Column out of sync

narayanyr · #1 (**permalink**) 10-14-10, 07:47

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

r937 · #2 (**permalink**) 10-14-10, 09:54

Quote:

Originally Posted by narayanyr

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

it-iss.com · #3 (**permalink**) 10-14-10, 10:14

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)

healdem · #4 (**permalink**) 10-14-10, 10:43

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.