CODEWITHSUNDEEP
sqlruby-on-railsdatabase
Trevoke
Trevoke

Reputation: 4117

Rails way to reset seed on id field

I have found the "pure SQL" answers to this question. Is there a way, in Rails, to reset the id field for a specific table?
Why do I want to do this? Because I have tables with constantly moving data - rarely more than 100 rows, but always different. It is up to 25k now, and there's just no point in that. I intend on using a scheduler internal to the Rails app (rufus-scheduler) to run the id field reset monthly or so.

Upvotes: 49

Views: 34351

Answers (9)

tbushman
tbushman

Reputation: 21

There are CounterCache methods:
https://www.rubydoc.info/docs/rails/4.1.7/ActiveRecord/CounterCache/ClassMethods
I used Article.reset_counters Article.all.length - 1 and it seemed to work.

Upvotes: 0

Štefan Bartoš
Štefan Bartoš

Reputation: 571

Rails way for e.g. MySQL, but with lost all data in table users:

ActiveRecord::Base.connection.execute('TRUNCATE TABLE users;')

Maybe helps someone ;)

Upvotes: 1

AlexChaffee
AlexChaffee

Reputation: 8252

Based on @hgmnz 's answer, I made this method that will set the sequence to any value you like... (Only tested with the Postgres adapter.)

# change the database sequence to force the next record to have a given id
def set_next_id table_name, next_id
  connection = ActiveRecord::Base.connection
  def connection.set_next_id table, next_id
    pk, sequence = pk_and_sequence_for(table)
    quoted_sequence = quote_table_name(sequence)
    select_value <<-end_sql, 'SCHEMA'
      SELECT setval('#{quoted_sequence}', #{next_id}, false)
    end_sql
  end
  connection.set_next_id(table_name, next_id)
end

Upvotes: 3

kikito
kikito

Reputation: 52668

I came out with a solution based on hgimenez's answer and this other one.

Since I usually work with either Sqlite or PostgreSQL, I've only developed for those; but extending it to, say MySQL, shouldn't be too troublesome.

Put this inside lib/ and require it on an initializer:

# lib/active_record/add_reset_pk_sequence_to_base.rb
module ActiveRecord
  class Base
    def self.reset_pk_sequence
      case ActiveRecord::Base.connection.adapter_name
      when 'SQLite'
        new_max = maximum(primary_key) || 0
        update_seq_sql = "update sqlite_sequence set seq = #{new_max} where name = '#{table_name}';"
        ActiveRecord::Base.connection.execute(update_seq_sql)
      when 'PostgreSQL'
        ActiveRecord::Base.connection.reset_pk_sequence!(table_name)
      else
        raise "Task not implemented for this DB adapter"
      end
    end     
  end
end

Usage:

Client.count # 10
Client.destroy_all
Client.reset_pk_sequence
Client.create(:name => 'Peter') # this client will have id=1

EDIT: Since the most usual case in which you will want to do this is after clearing a database table, I recommend giving a look to database_cleaner. It handles the ID resetting automatically. You can tell it to delete just selected tables like this:

DatabaseCleaner.clean_with(:truncation, :only => %w[clients employees])

Upvotes: 59

David Oneill
David Oneill

Reputation: 13085

You could only do this in rails if the _ids are being set by rails. As long as the _ids are being set by your database, you won't be able to control them without using SQL.

Side note: I guess using rails to regularly call a SQL procedure that resets or drops and recreates a sequence wouldn't be a purely SQL solution, but I don't think that is what you're asking...

EDIT:

Disclaimer: I don't know much about rails.

From the SQL perspective, if you have a table with columns id first_name last_name and you usually insert into table (first_name, last_name) values ('bob', 'smith') you can just change your queries to insert into table (id, first_name, last_name) values ([variable set by rails], 'bob', 'smith') This way, the _id is set by a variable, instead of being automatically set by SQL. At that point, rails has entire control over what the _ids are (although if it is a PK you need to make sure you don't use the same value while it's still in there).

If you are going to leave the assignment up to the database, you have to have rails run (on whatever time schedule) something like: 

DROP SEQUENCE MY_SEQ;
CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1;

to whatever sequence controls the ids for your table. This will get rid of the current sequence, and create a new one. This is the simplest way I know of you 'reset' a sequence.

Upvotes: 1

cwninja
cwninja

Reputation: 9778

I assume you don't care about the data:

def self.truncate!
  connection.execute("truncate table #{quoted_table_name}")
end

Or if you do, but not too much (there is a slice of time where the data only exists in memory):

def self.truncate_preserving_data!
  data = all.map(&:clone).each{|r| raise "Record would not be able to be saved" unless r.valid? }
  connection.execute("truncate table #{quoted_table_name}")
  data.each(&:save)
end

This will give new records, with the same attributes, but id's starting at 1.

Anything belongs_toing this table could get screwy.

Upvotes: 5

hgmnz
hgmnz

Reputation: 13306

You never mentioned what DBMS you're using. If this is postgreSQL, the ActiveRecord postgres adapter has a reset_pk_sequences! method that you could use:

ActiveRecord::Base.connection.reset_pk_sequence!('table_name')

Upvotes: 137

Jonas Elfstr&#246;m
Jonas Elfstr&#246;m

Reputation: 31438

No there is no such thing in Rails. If you need a nice ids to show the users then store them in a separate table and reuse them.

Upvotes: 1

MattMcKnight
MattMcKnight

Reputation: 8290

One problem is that these kinds of fields are implemented differently for different databases- sequences, auto-increments, etc.

You can always drop and re-add the table.

Upvotes: 1

Related Questions