setting id auto_increment to a value

[Anon]

I have the id of records in a table to auto increase and also set as a primary key:

id int DEFAULT '0' NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),

So when a record entry is made to the database, the id number is increased.. so say I have 5 records, which have ids 1 to 5 and I delete the last 3 so I only have records with ids and data in 1,2 and 3.. the next record that is added will have the id of 6. Why is this? and how can I set the id to decline if a record at the end of the table is deleted, and if a record in the middle of the table is deleted (ie they all slide down an id value)?

Thanks

Clint

[brandonschnell]

the purpose of the auto_increment column type is to guarantee that every row has a unique value, not that they're in order. if the rows were to be placed in order this would slow down mysql as it would have to find an unused id for every insert (imagine a table scan before every insert). the other problem is referencing rows by id could refer to the wrong row. its better for an error to occur if a deleted row is referenced then no error and the user working with the wrong row.

[Anon]

I getcha...

Thanks