On 2006-01-29 17:15:15 -0500, Steven Lembark wrote: > -- Dilly raja <[EMAIL PROTECTED]> > > >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.
>
> 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.
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.
Using the highest used value + 1 only helps if you only delete records
from 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.
hp
--
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpyGRSvuePhm.pgp
Description: PGP signature
