Rails - Mar 2006 - delete_all not resetting auto incement in database

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)

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)

-- 
Posted via http://www.ruby-forum.com/.

On 3/29/06, Steve Koppelman <hatlessnyc@yahoo.com>
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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060329/562a7aa8/attachment-0001.html