Forum: Ruby on Rails delete_all not resetting auto incement in database

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jesper Rønn-Jensen (Guest)
on 2006-03-29 16:42
(Received via mailing list)
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/ (weblog)
Steve K. (Guest)
on 2006-03-29 17:34
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/ (weblog)
Jesper Rønn-Jensen (Guest)
on 2006-03-29 17:47
(Received via mailing list)
On 3/29/06, Steve K. <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/ (weblog)
This topic is locked and can not be replied to.