26 replies to this topic

[dhi]

badinfluence, on 07 September 2005 - 09:54 PM, said:

hi,
Can we reset the id auto increment in the mysql database using by phpMyadmin?
let's say the shoutbox. the id #number table.. somehow i deleted some rows.. and i wanted to re-order/reset the id number acendingly in order rather than skip out..
thks

_{ALTER TABLE `table_name` AUTO_INCREMENT=1}

[k_nitin_r]

If you are about to export your data and load it into a re-created table, hold your horses. You do not have to go through all of that effort and you might actually corrupt your data while in the process if it is not performed correctly.

You can reset the auto increment to start with a different value, however you cannot set it to a value less than the maximum value in the column. You can find the highest value in the column by using a SELECT query with the MAX group function and then set the auto_increment value to a number one value higher using an ALTER TABLE statement.

Here is an example. Assume that you have a table named "department" that is defined as "create table department (id int auto_increment, name varchar(16));"

Next, insert some rows into the department table using the statements "insert into department(name) values('administration');" and "insert into department(name) values('sales');" This should use the IDs one and two. You can check this by selecting the rows from the table using a "select * from department;". This should display the values: 1 - administration, and 2 - sales. Now, assume that you deleted the sales row accidentally with a "delete from department where name = 'sales';". You are now left with exactly one row, "1 - administration". Suppose you want to re-create the sales row with the same ID, you know that the highest ID is 1, but just to double-check, execute the query: "select max(id) from department;"

To set the auto_increment to 2, type in the query "alter table department auto_increment = 2;"

All of these SQL statements can be executed in phpMyAdmin or just about any other SQL interface to MySQL, including the MySQL command line utility.

[Quatrux]

I can add that you need to change the auto increment value only when you finished testing your working software and when you make it public, forget the primary key values, it's not for you, it's for the database, don't care about it, if you want ordered numbers or something like that, you can add an extra column and use it, leave the primary keys, foreign keys alone after the software has gone public, just remember to reset it after your tests, or unit test or etc. before making it public by truncating the database and making the application available for everyone, it's an unique row number and it's a bad practice to make nr. 5 to nr. 2 as it looses it's unique value, why people worry about it? Who cares if the primary key is 451 5489416 154983 15496 or 1 2 3 4, it doesn't make any difference the the application if you code well.