Using Rails, how can I set my primary key to not b

Question

I'm using Rails migrations to manage a database schema, and I'm creating a simple table where I'd like to use a non-integer value as the primary key (in particular, a string). To abstract away from my problem, let's say there's a table employees where employees are identified by an alphanumeric string, e.g. "134SNW".

I've tried creating the table in a migration like this:

create_table :employees, {:primary_key => :emp_id} do |t|
    t.string :emp_id
    t.string :first_name
    t.string :last_name
end

What this gives me is what seems like it completely ignored the line t.string :emp_id and went ahead and made it an integer column. Is there some other way to have rails generate the PRIMARY_KEY constraint (I'm using PostgreSQL) for me, without having to write the SQL in an execute call?

NOTE: I know it's not best to use string columns as primary keys, so please no answers just saying to add an integer primary key. I may add one anyway, but this question is still valid.

Answer 1

I have one way of handling this. The executed SQL is ANSI SQL so it will likely work on most ANSI SQL compliant relational databases. I have tested that this works for MySQL.

Migration:

create_table :users, :id => false do |t|
    t.string :oid, :limit => 10, :null => false
    ...
end
execute "ALTER TABLE users ADD PRIMARY KEY (oid);"

In your model do this:

class User < ActiveRecord::Base
    set_primary_key :oid
    ...
end

Answer 2

In Rails 5 you can do

create_table :employees, id: :string do |t|
  t.string :first_name
  t.string :last_name
end

See create_table documentation.

Answer 3

I have tried it in Rails 4.2. To add your custom primary key, you can write your migration as :

# tracks_ migration
class CreateTracks < ActiveRecord::Migration
  def change
    create_table :tracks, :id => false do |t|
      t.primary_key :apple_id, :string, limit: 8
      t.string :artist
      t.string :label
      t.string :isrc
      t.string :vendor_id
      t.string :vendor_offer_code

      t.timestamps null: false
    end
    add_index :tracks, :label
  end
end

While looking at the documentation of column(name, type, options = {}) and read the line :

The type parameter is normally one of the migrations native types, which is one of the following: :primary_key, :string, :text, :integer, :float, :decimal, :datetime, :time, :date, :binary, :boolean.

I got the above ides as i have shown. Here is the table meta data after running this migration :

[arup@music_track (master)]$ rails db
psql (9.2.7)
Type "help" for help.

music_track_development=# \d tracks
                    Table "public.tracks"
      Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
 apple_id          | character varying(8)        | not null
 artist            | character varying           |
 label             | character varying           |
 isrc              | character varying           |
 vendor_id         | character varying           |
 vendor_offer_code | character varying           |
 created_at        | timestamp without time zone | not null
 updated_at        | timestamp without time zone | not null
 title             | character varying           |
Indexes:
    "tracks_pkey" PRIMARY KEY, btree (apple_id)
    "index_tracks_on_label" btree (label)

music_track_development=#

And from Rails console :

Loading development environment (Rails 4.2.1)
=> Unable to load pry
>> Track.primary_key
=> "apple_id"
>>

Answer 4

I know this is an old thread I stumbled across... but I'm kind of shocked no one mentioned DataMapper.

I find if you need to stray out of the ActiveRecord convention, I've found that it is a great alternative. Also its a better approach for legacy and you can support the database "as-is".

Ruby Object Mapper (DataMapper 2) holds a lot of promise and build on AREL principles, too!

Answer 5

It looks like it is possible to do using this approach:

create_table :widgets, :id => false do |t|
  t.string :widget_id, :limit => 20, :primary => true

  # other column definitions
end

class Widget < ActiveRecord::Base
  set_primary_key "widget_id"
end

That will make the column widget_id the primary key for the Widget class, then it is up to you to populate the field when objects are created. You should be able to do so using the before create callback.

So something along the lines of

class Widget < ActiveRecord::Base
  set_primary_key "widget_id"

  before_create :init_widget_id

  private
  def init_widget_id
    self.widget_id = generate_widget_id
    # generate_widget_id represents whatever logic you are using to generate a unique id
  end
end

Answer 6

I am on Rails 2.3.5 and my following way works with SQLite3

create_table :widgets, { :primary_key => :widget_id } do |t|
  t.string :widget_id

  # other column definitions
end

There is no need for :id => false.