List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 28 1999 2:29am
Subject:Re: Primary Key Renumbering... bug?
View as plain text  
At 9:57 PM -0400 4/27/1999, Dave Crawford wrote:
>I have a small table as follows:
>| Field  | Type         | Null | Key | Default | Extra          |
>| ID     | int(11)      |      | PRI | 0       | auto_increment |
>| name   | varchar(25)  | YES  |     | NULL    |                |
>| state  | char(2)      | YES  |     | NULL    |                |
>I had 50 some records inserted and was testing some delete commands:
>delete from table where (ID > 40);
>When I did a 'select * from table' I noticed that the remaining
>records had their ID's renumbered starting from 1. Is this a bug?
>The manual state's "If you delete the row containing the maximum
>value for an AUTO_INCREMENT column, the value will be reused. If
>you delete all rows in the table, the sequence starts over." But
>I didn't delete all the rows, so why did it renumber them? Normally
>it wouldn't be a big deal but I'm assigning a 'User #' that correlates
>the ID number, so I need the ID column to always remain relative to the
>original record. Thanks in advance.

I don't see the problem.  When you inserted the rows, presumably
the ID fields were set to values from 1 to 50.  You only deleted
rows with ID > 40, so the remaining records would be those with
ID values from 1 to 40.  They weren't renumbered at all.

Paul DuBois, paul@stripped
Northern League Chronicles:
Primary Key Renumbering... bug?Dave Crawford28 Apr
  • Re: Primary Key Renumbering... bug?Paul DuBois28 Apr
  • Re: Primary Key Renumbering... bug?Sasha Pachev28 Apr
Re: Primary Key Renumbering... Bug?Dave Crawford28 Apr
  • Re: Primary Key Renumbering... Bug?Paul DuBois28 Apr
  • Re: Primary Key Renumbering... Bug?Dave Crawford29 Apr
  • Re: Primary Key Renumbering... Bug?Benjamin Pflugmann29 Apr