MySQL Forums
Forum List  »  Newbie

Re: AUTO_INCREMENT
Posted by: Felix Geerinckx
Date: April 22, 2005 07:57AM

nizar a wrote:

> I do the following
> ALTER TABLE emails ADD num INT AUTO_INCREMENT FIRST, ADD UNIQUE KEY(num);
> It works great but if I delete one of the recordsthe 'num' column isn't updated,

Let's all be glad it isn't doing that ;-)
AUTO_INCREMENT's are normally used as primary keys, which should never change nor be reused when deleted.

> example.
> 1 f l f_l@email.dom
> 2 ab cd ad@bd.dom
> 3 ef gh eh@fg.com
>
> When 2 ab cd ad@bd.dom is deleted the DB looks like
> 1 f l f_l@email.dom
> 3 ef gh eh@fg.com
>
> I want it to be
> 1 f l f_l@email.dom
> 2 ef gh eh@fg.com
>
> Is it possible without deleting the 'num' column
> and adding it again everytime I delete a row?

If you do want a 1-2-3 counter for whatever reason, you can use a user variable:

SET @counter = 0;
SELECT
@counter := @counter + 1 AS c,
some_field
FROM foo
ORDER BY some_other_field;

--
felix

Options: ReplyQuote


Subject
Written By
Posted
April 22, 2005 07:15AM
Re: AUTO_INCREMENT
April 22, 2005 07:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.