ansaurus

Question

Setting initial value for primary key in Rails (i.e. first object's id is 1000 instead of 1)

Answer 1

+4 A:

This is database specific. Just do something like the following in your migration:

class MyMigration < ActiveRecord::Migration
  def self.up
    create_table :my_table do |t|
      # ...
    end
    execute "ALTER TABLE my_table AUTO_INCREMENT = 1000" # for MySQL
  end

  def self.down
    # ...
  end
end

Or indeed, even better, as suggested by Bane:

  def self.up
    create_table :my_table, :options => "AUTO_INCREMENT = 1000" do |t|
      # ...
    end
  end

Be cautious with database-specific migrations, though! Using any SQL that is specific to your database will break compatibility with other databases and is generally not a good idea.

molf 2009-06-23 13:38:18

Migrations should be database independent. What if the database you are running migration on doesn't support that option? The execution will probably raise an exception. I would run the command directly on the database instead. BTW, I agree with the solution.

Simone Carletti 2009-06-23 13:49:19

@weppos, running it directly on a database will rob you of the advantage of migrations that they can be used to easily port your database schema to other servers. Of course, the other major advantage, porting to a database from a different vendor, will be void.

molf 2009-06-23 13:55:00

Hi molf, you're missing a double quote before AUTO_INCREMENT.

Barry Gallagher 2009-06-23 13:57:05

And of course one can argue that setting the auto-increment value is not actually part of the db schema. Because it is apparently a requirement in this case I would assert this behaviour in a test case somewhere. In which case it would be necessary in the migrations as well.

molf 2009-06-23 13:59:34

@Bane, thanks, fixed!

molf 2009-06-23 14:00:05

Answer 2

+4 A:

Any string passed into the ":options" option will be appended to the end of the SQL statement that creates the table. Best practice.

def self.up
    create_table :my_table, :options => "AUTO_INCREMENT = 1000" do |t|
      # ...
    end
end

Barry Gallagher 2009-06-23 13:46:01

ansaurus

tags:

views:

answers:

Setting initial value for primary key in Rails (i.e. first object's id is 1000 instead of 1)

related questions