Refresh row positions after deleting one?

Let's say I have a table with an "ID" field that's set to auto increment. By default if I delete a row, lets say with ID=3, the table now has a gap in terms of the ID column:

+--------+-------------+---+
| name | place | id |
+--------+-------------+----+
| kelly | market | 1 |
| albert | home | 2 |
| kelly | arena | 4 |
| kelly | arena | 5 |
+--------+--------------+---+

I was hoping there's a way to easily refresh the table so it's 1, 2, 3, 4 again, automatically filling in the gap where "id=3" used to be with the remaining rows.

Thanks,

ALTER TABLE tablename AUTO_INCREMENT = 1

If your auto increment field is a primary key relating other tables then I highly recommend not doing that. The reason it does that is to prevent a relational database from becoming corrupted when used as a key to relate tables together.

Thanks for the reply. I've tried your suggestion, and while the command ran ok, it didn't seem to alter the behavior of the table. Deleting a record in the middle of the table (ie: ID=2) and then inserting a new record still creates one with an ID of the old table's max(id)+1, with ID=2 being skipped:

ID field: 1, 3, 4, 5, 6

for example.

I'll play around a little more. After all, that's just what I'm doing now anyway. It just seems that if an auto incrementing primary key cannot collapse after a record has been deleted, after a long time, you could end up with max(id)= millions, with gaps all over the place in between. That just doesn't sound right.

Hmmm. Should have worked.

What I was mentioning before was that if you change the value of a primary key field and that field is used to relate other tables then you would lose references to records in other related tables. Like for instance if your pk value was 3 and that 3 was stored in another table relating a record and you suddenly reset the pk and that 3 gets changed and no longer exists or get assigned to a different row then now either that related record no longer has a reference or it is now related to the wrong record.

It does seem strange to have gaps but that is how it was designed; to help prevent corrupting the database. If you are planning on having millions of records in the database you are probably better off using a real RDBMS like Oracle. But you can also set that auto increment field as an index field as well to speed up finding records.

Originally posted by Spookster
If you are planning on having millions of records in the database you are probably better off using a real RDBMS like Oracle.
Not necessarily. What I mean is, lets say I write a guestbook script that gets 100 entries a day, or 3,000 a month. I may prune the guestbook every week for 30% of the oldest posts, but since the ID is auto incrementing, this field will keep growing regardless. That means in a few years, it will be millions, even though the guestbook remains at around few hundred or a few thousand records. The ID field will simply be 1 million 1 hundred to 1 million 6 hundred, with gaps all over the place.

I understand the reason mysql doesn't auto fill in the blanks is to perserve the integrity of the primary key, though what if ID isn't the primary key, or even if it is, the coder wishes to write custom code that adjusts to this (kind of like a transaction).

I'll definitely play around a little more with your earlier solution (alter table...). Thanks!

If you are really concerned about that, then you could simply run these commands to "reset" that column. Simply put:

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name ADD COLUMN column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

If you are really concerned about that, then you could simply run these commands to "reset" that column. Simply put:

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name ADD COLUMN column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

That worked great, but what would be need would be if any dependant tables could be mapped to the new values