Deleting the Unique ID in MySQL

Can Delete the Row, but Primary ID doesn't disappear

[jimh009]

Hi,

Very stupid question, I know.

But, I've been messing with one of my tables in the database for testing purposes, and now need to delete the stuff in there. I can do this simply enough with the following:

Delete FROM my_table WHERE id<=2

This removes all the records except the first one without problems. Unfortunately, the primary ID does not get "reset" - instead, it keeps counting up. As such, I have one record in this table and any new records that are added to the table having a Primary ID starting at 20.

So, how can I delete a row in MySQL that not just gets rid of the row but also the primary ID too (essentially re-setting the ID back to the lowest number not used)?

Or, is this not really an issue - and I'm just being anal?

Jim

[coopster]

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 2;

Resource:
Using AUTO_INCREMENT [dev.mysql.com]

Here is another discussion [webmasterworld.com] on the topic with some additional considerations you might find useful.

[stajer]

jimh - you can't do this without basically deleting and recreatign the table.

Imagine a table with 100 rows and you delete the first 20. It is unrealistic to have the other 80 renumbered to begin at 1.

What if you had identified line 95 before you deleted the 20. If you wanted that line, your code would have to pull line 95 - 20 = 75. Do you want to have to keep track of all that?

[aspdaddy]

Hi,you will need to write a db maintenance plan to periodically redo your indexes etc. It will need to loook at any table relations etc.

When you design a database this is one of the considerations you need to plan for and then decide natural vs surrogate keys. If you have used keys in any customer/client facing documents then obviously you cant change them.

For testing, just write a script that clears the tables followed by a call to reseed, dont know mySQL but in SQL thats:
DBCC checkident('tablename',RESEED,value)

[physics]

This removes all the records except the first one without problems. Unfortunately, the primary ID does not get "reset" - instead, it keeps counting up.

Personally I think this is a good thing. It means that there is no way you will accidentally refer to the wrong data in some other place.

Assume the counter does get reset when you delete something.
For instance say you have article 5343 which is about an G rated topic.
Someone links to example.com/5343.html
You find out the article has a factual error and you deltete it, counter is reset to 5343.
Then in another area of your site where you have R rated topic you add an article and it gets set to 5343.
Someone clicks the link thinking they'll get the G rated material and ends up seeing R rated material, oops!

This can also cause more innocent confusion which I've personally experienced when clicking links on some sites that do reset the counter.

If the counter keeps counting up this won't happen ...

But if you still prefer to have your counter 'reset' the best way would be to set the index numbers yourself instead of using auto-increment (at least AFAIK).

[jimh009]

Thanks everyone. Guess I was just being overcautious. I'll just leave things the way they are - its a small table and doesn't relate to any other tables, and just serves as a place to hold the information from the mini-CMS I developed for a new site.

Jim