List:General Discussion« Previous MessageNext Message »
From:Dave Crawford Date:April 28 1999 1:57am
Subject:Primary Key Renumbering... bug?
View as plain text  
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
to
the ID number, so I need the ID column to always remain relative to the
original record. Thanks in advance.
Thread
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