technology from back to front

Changing the Primary Key Type in Ruby on Rails Models

Ruby on Rails (RoR) likes to emphasise the concept of convention over configuration. Therefore, it seeks to minimialise the amount of configuration
by resorting to some defaults. These defaults are sometimes not desirable, and RoR does not always make it easy to deviate from these defaults.

Primary Keys Type

By default, all the models in RoR has a column called id of the type :primary_key. The various database adapters that RoR uses to talk to the databases will translate the type to the appropriate column type that the database supports. For example, in PostgreSQL, this would be serial primary key.

There are often times when you would like to change the primary key of tables to be of a different type (such as when you are using some generated UDID as
the primary key), or you simply want to use multiple columns as the primary key. The latter problem can be solved by using
a Ruby Gem but the former is not so easily solved.

Stackoverflow provides a solution to change the type of the primary key, but it is lacking in
some ways. Primarily, the schema file generated by Rails will now omit the necessary SQL instructions to actually make the custom primary key
column a primary key in the database.

Our Workaround

The first step to changing the primary key involves the migration file.
The span used is similar whether you are changing an existing table or if you are creating a new table.

Migration

A migration file might look like this:

class ChangePrimaryKey < ActiveRecord::Migration
  def up
    remove_column :table, :id # remove existing primary key
    rename_column :table, :udid, :id # rename existing UDID column
    execute "ALTER TABLE table ADD PRIMARY KEY (id);"
  end

  def down
    # Remove the UDID primary key. Note this would differ based on your database
    execute "ALTER TABLE table DROP CONSTRAINT table_pkey;"
    rename_column :table, :id, :udid
    add_column :table, :id, :primary_key
  end
end

If you are creating a new table, your migration might look like this:

class AddTableWithDifferentPrimaryKey < ActiveRecord:Migration
  def change
    create_table :table, id: false do |t|
      t.string :id, null: false
      # other columns
      t.timestamps
      execute "ALTER TABLE table ADD PRIMARY KEY (id);"
    end
  end
end

Notice the id: false options you pass into the table — this asks Rails not to create a primary key column on your behalf.

Changes to Model

In the model, it is essential that you add the following line in order for
Rails to programmatically find the column you intend to use as your primary key.

class Table < ActiveRecord::Base
  self.primary_key = :id

  # rest of span
end

Database Schema

You might now notice that the db/schema.rb file generated by Rails will
look like this:

create_table 'table' id: false, force: true do |t|
  t.string 'id', null: false

  # ... the rest
end

When you invoke rake db:schema:load to set up a new machine, the table
you create will lack a primary column constraint. This is because the custom
execute query you added to the migration will never get run, and Rails does
not know what goes on in those execute statement.

A hacky solution we came up with was to add a rake task to be run after
rake db:schema:load is run.

In your lib/tasks directory, add a file like after_db_schema_load.rake:

# NOTE: Rails does not allow other primary keys to be defined so we have
# to do it here

namespace :your_app do
  namespace :db do
    task :after_schema_load => :environment do
      puts 'Adding primary key for :documents'
      query = 'ALTER TABLE table ADD PRIMARY KEY (id);'
      ActiveRecord::Base.connection.execute(query)
    end
  end
end

Rake::Task['db:schema:load'].enhance do
  ::Rake::Task['docweb:db:after_schema_load'].invoke
end

Whenever you run rake db:schema:load, the primary key constraint will now
be set accordingly.

by
Yong Wen Chua
on
30/09/13
  1. findchris
    on 29/01/14 at 2:59 am

    Here is a working solution without the hacks:
    http://stackoverflow.com/a/15297616/679628

 
 


− seven = 2

2000-14 LShift Ltd, 1st Floor, Hoxton Point, 6 Rufus Street, London, N1 6PE, UK+44 (0)20 7729 7060   Contact us