Reg: auto_increment issue.

------=_Part_9090_32865003.1138423571185
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I created a table in mysql in which i set a field "id" in that table to
auto_increment and this field is the primary key to this tabel. so each
time a new record is added the id increment by 1. When i delete a last
record and insert the next record it increment the value from the deleted
record only instead of the last existing record. Is there any solution in
which i can set these values or reset the auto_increment so that it starts
from the first when i delete all the record.


--
Friendly,
Raja.M

------=_Part_9090_32865003.1138423571185--

Re: Reg: auto_increment issue.

> I created a table in mysql in which i set a field "id" in that table to
> auto_increment and this field is the primary key to this tabel. so each
> time a new record is added the id increment by 1. When i delete a last
> record and insert the next record it increment the value from the deleted
> record only instead of the last existing record. Is there any solution in
> which i can set these values or reset the auto_increment so that it starts
> from the first when i delete all the record.

Using Google and "mysql auto-increment reset dbi" I found:

On this page http://www.debian-administration.org/articles/64
To reset the autoincrement values of your table run:
truncate table TABLENAME
This will cause the id to start from zero again.

Over at MySQL.COM I find things like
3.6.9. Using AUTO_INCREMENT
To start with an AUTO_INCREMENT value other than 1, you can set
that value with CREATE TABLE or ALTER TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
Note that this feature is available for InnoDB tables only as
of MySQL 4.1.12.

You might want to look around in the MySQL manual to see where they
describe exactly how the auto-increment 'counter' is set by MySQL.

I hope one of the above two ideas helps you, and Google is always helpful.
(If my books weren't packed I'd look this up in my "MySQL Cookbook" book)

> --
> Friendly,
> Raja.M
>

Re: Reg: auto_increment issue.

On Sat, 28 Jan 2006 10:16:11 +0530, Dilly raja wrote:

Hi Raja

> record. Is there any solution in which i can set these values or

Yes, but you're wasting your time worrying about magic numbers.

You should be writing your code such that it does /not/ depend on sequences=

having any particular values.
--
Cheers
Ron Savage, ron@savage.net.au on 29/01/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: Reg: auto_increment issue.

-- Dilly raja

> I created a table in mysql in which i set a field "id" in that table to
> auto_increment and this field is the primary key to this tabel. so each
> time a new record is added the id increment by 1. When i delete a last
> record and insert the next record it increment the value from the deleted
> record only instead of the last existing record. Is there any solution in
> which i can set these values or reset the auto_increment so that it starts
> from the first when i delete all the record.

This is not a DBI issue at all, but one for MySQL.
You'll probably get better answers in the future
on a MySQL mailing list than this one for database
issues.

Auto-increment fields are really indended for use
as surrogate keys. As such, they are not intended
for use on tables that will have records deleted
from them. You might do better to create a single
table, call it "sequence" with a single unsigned
integer and select its value for update, increment,
then store it using DBI when you add records. If
you are adding multiple records and have the count
in advance then increment the sequence by that
number instead of one to get a bulk list.


--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508

Re: Reg: auto_increment issue.

--Cgrdyab2wu3Akvjd
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2006-01-29 17:15:15 -0500, Steven Lembark wrote:
> -- Dilly raja
>=20
> >I created a table in mysql in which i set a field "id" in that table to
> >auto_increment and this field is the primary key to this tabel. so each
> >time a new record is added the id increment by 1. When i delete a last
> >record and insert the next record it increment the value from the deleted
> >record only instead of the last existing record.

I believe this depends on engine you are using (i.e., its different
between MyISAM and InnoDB)

> >Is there any solution in which i can set these values or reset the
> >auto_increment so that it starts from the first when i delete all the
> >record.
>=20
> This is not a DBI issue at all, but one for MySQL.
> You'll probably get better answers in the future
> on a MySQL mailing list than this one for database
> issues.
>=20
> Auto-increment fields are really indended for use
> as surrogate keys. As such, they are not intended
> for use on tables that will have records deleted
> from them.

This doesn't follow. The use of surrogate keys has nothing to do with
whether it makes sense to delete records from a table or not. You use
surrogate keys if you don't have a primary key in your data.

However, surrogate keys are supposed to be unique, but not necessarily
dense. It is entirely possible to have holes in the sequence. Normally,
this shouldn't matter. If it does, you will have to find a different
approach.=20

Using the highest used value + 1 only helps if you only delete records
=66rom the end. Suppose you have records with the keys 1 to 7. If you
delete record #7 and insert a new one, it might again get key 7.
However, if you delete record #4, and then insert a new record, it will
get key 8, and you still have the hole at #4. So if you need a dense
numbering, you probably need to renumber the whole table every time a
record is deleted (and then the "sequence number" field should not be
the primary key).

> You might do better to create a single table, call it "sequence" with
> a single unsigned integer and select its value for update, increment,
> then store it using DBI when you add records.

That doesn't really solve the problem.=20

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--Cgrdyab2wu3Akvjd
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQ94Cq1LjemazOuKpAQF4VAXUC5bnrb4QWhcaBVT6yGnyr8hf9Ru6 tNov
RlQdseTiRBBpDoxFDJDp6E81tg43vMF+AgexT+/S+Y45Z92lyHqpUWCm9dYd G28m
dSqyyIPWiDqPlV7vGYfi8nb6BgFfMHMY/myJ73PKcyR3BoL0+vrext5y3EdT r0Yl
0eGtZJnltv9hrG5dLxSc6Xk/+xU2n53vTm6bFtca9PaCG0wCa9Ag+/isDZtz yf4X
77rXq9bUEbeNqzTCVL3PHzUnpg==
=T5Ei
-----END PGP SIGNATURE-----

--Cgrdyab2wu3Akvjd--