Auto-Increment Values in Mysql

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.


Thanks & best Regards
Adarsh Sharma

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Auto-Increment Values in Mysql

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Auto-Increment Values in Mysql

--------------enig3207AA3903A524E2DB7189BC
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

You do not want this really from the point you understood
what a primary key does in a database

The PRIKEY is unqiue for a record and if the record
does no longer exist his PRIKEY must never return

Sample:
* website
* shop-products
* you have prodid 500 costs 200$
* you delete the product
* there are bookmarks out there
* they NEVER should point to another product what costs 5.000 $ as sample=


in no rdbms auto_increment values are reused and if than
you have found a hard bug

Am 09.02.2011 12:41, schrieb Adarsh Sharma:
> Dear all,
>=20
> I have an auto-increment column in Mysql database table.=20
> Let's say the column has below values :
> Now if i deleted some rows where id=3D 3 ,5 and 8
>=20
> The data look like as :
>=20
> 1
> 2
> 4
> 6
> 7
> 9
> 10
>=20
> 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.

--=20

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/


--------------enig3207AA3903A524E2DB7189BC
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk1ShmoACgkQhmBjz394AnmhXQCffhoGiNSIFt9Z6RFQJCgJ 8+u7
EGoAnjitaNK67nGgc54nfOQ2phRLbm/o
=7J/I
-----END PGP SIGNATURE-----

--------------enig3207AA3903A524E2DB7189BC--

Re: Auto-Increment Values in Mysql

> From: Adarsh Sharma
>=20
> I have an auto-increment column in Mysql database table. Let's say the =
column has below values :

I'll echo what others have said.

Auto-increment is typically used to generate unique primary keys. If =
this column is your primary key, DO NOT change its value! PK =3D =
identity.

"The value of a record is the key, the whole key, and nothing but the =
key, so help me Codd." :-)

If the auto-inc field is NOT the primary key, AND if it must have =
contiguous values, as others said, you probably want to have your =
business logic keeping track of it, rather than using auto-increment.

I know others have said the same thing, but sometimes it help to hear it =
put differently.

----------------
In my mind are many dwellings. Each of the dwellings we create ourselves =
- the house of anger, the house of despair, the house of self pity, the =
house of indifference, the house of negative, the house of positive, the =
house of hope, the house of joy, the house of peace, the house of =
enthusiasm, the house of cooperation, the house of giving. Each of these =
houses we visit each day. We can stay in any house for as long as we =
want. We can leave these mental houses any time we wish. We create the =
dwelling, we stay in the dwelling, we leave the dwelling whenever we =
wish. We can create new rooms, new houses. Whenever we enter these =
dwellings, this becomes our world until we leave for another. What world =
will we live in today? -- Don Coyhis
:::: Jan Steinman, EcoReality Co-op ::::


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg