List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 8 2001 4:52am
Subject:Re: AUTO_INCREMENT Confusion
View as plain text  
At 12:14 AM -0400 5/8/01, Kevin McBrearty wrote:
>I'm a bit confused about AUTO-INCREMENT behaviour. I have a table with an
>AUTO-INCREMENT column defined as int unsigned primary key. Records are
>regularly added and then later deleted from this table. If the last record
>is deleted, the sequence begins at 1 again.
>Reading from MySQL (Paul Dubois) pages 94-95, it says that "the values in an
>automatically generated series are strictly increasing and not reused", even
>if the maximum value is deleted. This applies to 3.23 and up. The sequence
>is restarted only if you delete all records from the table using the DELETE
>FROM tbl_name syntax.
>I am deleting row by row (DELETE FROM tbl_name WHERE...). Shouldn't the
>sequence be preserved, i.e. if the last record in the table has value 43 and
>I delete it, shouldn't the next record get 44 ?

Yes, it should.  I just tried it and it works.  But then I changed the
table to type BDB and got the behavior you're describing, so it appears
the behavior differs betweeen table types.  Are you using a BDB table?

>I am running 3.23.37 on Suse 7.0
>Kevin McBrearty
>ATG Automation Technologies Group Ltd.
>Before posting, please check:
>   (the manual)
>           (the list archive)
>To request this thread, e-mail <mysql-thread73248@stripped>
>To unsubscribe, e-mail <>
>Trouble unsubscribing? Try:

Paul DuBois, paul@stripped
AUTO_INCREMENT ConfusionKevin McBrearty8 May
  • Re: AUTO_INCREMENT ConfusionPaul DuBois8 May
    • RE: AUTO_INCREMENT ConfusionKevin McBrearty8 May
    • RE: AUTO_INCREMENT ConfusionKevin McBrearty8 May
RE: AUTO_INCREMENT ConfusionHeikki Tuuri8 May