Re: Reg: auto_increment issue.

On 2006-01-29 17:15:15 -0500, Steven Lembark wrote:
> -- Dilly raja <rajadilly@xxxxxxxxx>
>
> >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.
| | | hjp@xxxxxxxxx | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

Attachment:pgpIfGbg7JkWB.pgp
Description: PGP signature

Relevant Pages

  • Re: Reg: auto_increment issue.
    ... auto_increment and this field is the primary key to this tabel. ... 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. ... 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. ...
    (perl.dbi.users)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Doubling the order
    ... Other than the orderId, there is nothing to say that two orders are the ... > 1.PRODUCTID) - primary Key ... >> relational database and it is not the best one. ... the gap in the sequence is not filled in and the sequence ...
    (microsoft.public.sqlserver.programming)
  • Re: Indexes and primary keys, from Delaney
    ... bloat all nonclustered indexes and the likelihood of page splitting. ... If you have a clustered index on SSN,ACCOUNT,DATE, SEQUENCE ... > and account number, ... > I originally had all four fields as a composite primary key. ...
    (microsoft.public.sqlserver.programming)
  • Re: Doubling the order
    ... 1.PRODUCTID) - primary Key ... > data model or any data integrity. ... the gap in the sequence is not filled in and the sequence ... > Since a query result is a table, and a table is a set which has no ...
    (microsoft.public.sqlserver.programming)