List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:February 9 2011 11:54am
Subject:Re: Auto-Increment Values in Mysql
View as plain text  
On 09/02/2011 11:41, Adarsh Sharma wrote:
> Dear all,
>
>
> I have an auto-increment column in Mysql database table. Let's say the
> column has below values :
>
>
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
>
>
> Now if i deleted some rows where id= 3 ,5 and 8
>
> The data look like as :
>
> 1
> 2
> 4
> 6
> 7
> 9
> 10
>
> I want to have it id's as
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> and next data is inserted right at 8
>
> Please help how to achieve it.

Firstly, if this matters to you then an autoincrementing value is 
probably not what you should be using in the first place. The main point 
of autoincrement is that it doesn't matter what the actual value is, 
it's just a way of achieving a unique key for the table. If you care 
what the values are, then just use an INT field and generate the numbers 
yourself.

That said, if you want to renumber an autoincrementing field then the 
simplest way is to drop it and re-add it:

ALTER TABLE `mytable` DROP `myfield`;

ALTER TABLE `mytable` ADD `myfield` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Mark
-- 
http://mark.goodge.co.uk
http://www.ratemysupermarket.com
Thread
Auto-Increment Values in MysqlAdarsh Sharma9 Feb
  • Re: Auto-Increment Values in MysqlMark Goodge9 Feb
  • Re: Auto-Increment Values in MysqlReindl Harald9 Feb
Re: Auto-Increment Values in MysqlJan Steinman9 Feb