reset autoincrement counter

[dataDude]

In MySQL I set up a table with 5 columns (the number doesn't matter so much), the first one I have set to autoincrement. I have tested the table with INSERT and DELETE queries and have been successful with the tests. I've noticed, however, as the rest of you probably know, that even though I've deleted rows, the first column continues counting, even if the previous row was deleted.

i.e.:

1 row2 row3 row4 row5
3 row2 row3 row4 row5
4 row2 row3 row4 row5
6 row2 row3 row4 row5


How do I reset the counting, if possible?

[kstatefan40]

The pure SQL statement:

PHP Code:

ALTER TABLE tbl_name AUTO_INCREMENT 
= 1; 


[Force Flow]

Make sure there are no rows you do this, or if you do, change the 1 to one more than the highest row numbered.

[dataDude]

For now I found a way to solve it is to delete the column that autoincrements and then readd it and have it autoincrement. If anybody has a simpler way to solve it feel free to share.

[mairving]

Originally Posted by dataDude For now I found a way to solve it is to delete the column that autoincrements and then readd it and have it autoincrement. If anybody has a simpler way to solve it feel free to share.

If the table is empty it is pretty easy to do using either method. I think that I have even just forced a value in the auto-incremented field and that reset it. It the table isn't empty and/or the auto-incremented field is used as a foreign key in another table, you certainly don't want to do this.

[dataDude]

I did it before and it seemed to work. What's wrong with doing it that way? And no, the table is not empty, but I will make a note of the previous suggestion.

[faulkner132]

Why do you need to do this? Like Mairving said, it is generally not a good idea as only problems can occur.

[dataDude]

When I tested it I didn't see any issues that happened. I would imagine similar procedures would be done for those online companies using databases for various features and later have to add columns for additional features that would affect every member of such sites. Correct me if I'm wrong about that.