# Delete\_all not resetting auto incement in database **URL:** \ **Category:** Rails\ **Created:** [March 29, 2006, 2:42pm UTC](https://www.ruby-forum.com/t/delete-all-not-resetting-auto-incement-in-database/89954 "2006-03-29T14:42:51Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Jesper\_RSSSSnn-Jens](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/96bed5/32.png) [@Jesper\_RSSSSnn-Jens](https://www.ruby-forum.com/u/Jesper_RSSSSnn-Jens)\ **Post date:** [March 29, 2006, 2:42pm UTC](https://www.ruby-forum.com/t/delete-all-not-resetting-auto-incement-in-database/89954/1 "2006-03-29T14:42:51Z")
Hey I’m writing a simple admin interface thats reading user data (skills) from a comma separated file. To clear the database before inserting new skills, I use Skill.delete\_all Then I insert the new values I read from a file. Everything goes well up to the point where I see that auto-generated ID’s are not starting from 1. They start off at 840 (responding to the number of skills times the number of times I reloaded data). From my log I can see that the SQL query that’s performed is DELETE FROM… and not TRUNCATE (which resets the auto increment counter) It seems that Skill.delete\_all doesn’t reset the auto\_increment counter on the database table. Hope you can help me with my questions here: Q1: Is it a bug in Rails that Class.delete\_all does not reset auto\_incement counter? Q2: Is there a workaround that I can use? (for instance, how can I do an SQL query ‘TRUNCATE skills’) ## Any help appreciated! – Jesper Rønn-Jensen Capgemini Danmark A/S +45 23 73 62 20 (Mobile) [http://justaddwater.dk/](http://justaddwater.dk/) (weblog) ---
**Author:** ![Jesper\_RSSSSnn-Jens](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/96bed5/32.png) [@Jesper\_RSSSSnn-Jens](https://www.ruby-forum.com/u/Jesper_RSSSSnn-Jens)\ **Post date:** [March 29, 2006, 3:34pm UTC](https://www.ruby-forum.com/t/delete-all-not-resetting-auto-incement-in-database/89954/2 "2006-03-29T15:34:42Z")
It seems like a feature to me, not a bug. If the auto\_increment counter on the table was reset, there would be a risk of data in other tables that reference the Skills table pointing to incorrect new Skills once you start repopulating it. This way, if you still have other tables that reference old Skills and haven’t had their skill\_id fields nulled out or otherwise reset, they’ll instead return nil when you query on, for instance Employee[1].skills. This is probably a much more desirable result than a query that returns incorrect data. -sk Jesper Rønn-Jensen wrote: > Hey > I’m writing a simple admin interface thats reading user data (skills) > from a comma separated file. > To clear the database before inserting new skills, I use > Skill.delete\_all > Then I insert the new values I read from a file. Everything goes well > up to the point where I see that auto-generated ID’s are not starting > from 1. They start off at 840 (responding to the number of skills > times the number of times I reloaded data). > > From my log I can see that the SQL query that’s performed is DELETE > FROM… and not TRUNCATE (which resets the auto increment counter) > > It seems that Skill.delete\_all doesn’t reset the auto\_increment > counter on the database table. > > Hope you can help me with my questions here: > > Q1: Is it a bug in Rails that Class.delete\_all does not reset > auto\_incement counter? > > Q2: Is there a workaround that I can use? > (for instance, how can I do an SQL query ‘TRUNCATE skills’) > > ## Any help appreciated! > > – > Jesper Rønn-Jensen > Capgemini Danmark A/S > +45 23 73 62 20 (Mobile) > [http://justaddwater.dk/](http://justaddwater.dk/) (weblog) ---
**Author:** ![Jesper\_RSSSSnn-Jens](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/96bed5/32.png) [@Jesper\_RSSSSnn-Jens](https://www.ruby-forum.com/u/Jesper_RSSSSnn-Jens)\ **Post date:** [March 29, 2006, 3:47pm UTC](https://www.ruby-forum.com/t/delete-all-not-resetting-auto-incement-in-database/89954/3 "2006-03-29T15:47:17Z")
On 3/29/06, Steve K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > It seems like a feature to me, not a bug. If the auto\_increment counter > on the table was reset, there would be a risk of data in other tables > that reference the Skills table pointing to incorrect new Skills once > you start repopulating it. > … This is probably a much more desirable result than a query that returns > incorrect data. In this case I also clear out the other tables to make sure that all ID’s match. Feature or bug, I’m also interested in how I do a workaround, that is, firing an SQL query ‘TRUNCATE skills’ (which resets the auto incrementer). Thanks for your reply – Jesper Rønn-Jensen [http://justaddwater.dk/](http://justaddwater.dk/) (weblog)