Delete_all not resetting auto incement in database

Rails
1

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)

2

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)

3

On 3/29/06, Steve K. [email protected] 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)