Should I delete the default "id" field when using a string primary key in Rails / Postgres?

Question

This is a build on a question I asked before (without any luck).

I have a model where I'm using a string as a primary key:

class Employee < ActiveRecord::Base
   self.primary_key = "employment_id"
end 

This table also contains the Rails default 'id' field, with a uniqueness constraint.

When I add a new employee locally it all works fine, with Rails automatically generating a new unique id.

However when I run this on Heroku Postgres it appears to treat 'id' and 'employment_id' as the same field. I tried to get around this by manually setting a unique id, but still get this behaviour:

Employee.new do |s|

  max_id = Employee.maximum(:id)  
  puts max_id.to_s             # => 1803 

  s.employment_id = "fred_01"      
  s.id = max_id + 1 

  puts employment_id.to_s      # => 1804

end

I'm running postgres 9.1.3 locally (and Heroku is on 9.1.4). I'm on Rails 3.2.3.

My questions are:

• Any idea what's going on here?
• Do you think it's right that I'm using employment_id as the primary key?
• Would help if I deleted the 'id' field?
• Is there any other best practice you would recommend?

Thanks for your help!

Derek.

Edit:

Adding migration file as requested:

class CreateEmployees < ActiveRecord::Migration
  def change
    create_table :employees do |t|
      t.string :employment_id, :unique => true
      etc...
    end
  end
end

EDIT

Very kind of nobody to point this out, but it's clear to me now that the real answer is "Just because you can, doesn't mean you should!"

Answer 1

According to this post, it should solve the problem:

class CreateEmployees < ActiveRecord::Migration
  def change
    create_table :employees, {:id => false} do |t|
      t.string :employment_id, :unique => true
      etc...
    end
  end
  execute "ALTER TABLE employees ADD PRIMARY KEY (employment_id);"
end

Also in your model:

class Employee < ActiveRecord::Base
  set_primary_key :employment_id
  ...
end

Answer 2

An update for Rails 4.2.0 and Postgres in case it helps anyone...

Put a id: :string in the create_table options:

class FooMigration < ActiveRecord::Migration
  def change
    create_table :foo, id: :string do |t|
      t.string :name, null: false
      t.timestamps null: false
    end
  end
end

rake db:migrate

== 20150225043441 FooMigration: migrating ===========================
-- create_table(:foo, {:id=>:string})    -> 0.0031s
== 20150225043441 FooMigration: migrated (0.0032s) ==================

rails db (and \d foo)

psql (9.3.6)
Type "help" for help.

bar_development=# \d foo
                  Table "public.foo"
   Column   |            Type             | Modifiers 
------------+-----------------------------+-----------
 id         | character varying           | not null
 name       | character varying           | not null
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

I end up with a 'not null' string column with a primary key index.

Using a column name other than id is more involved.

Edit (26 Feb 2015): there seems to be a bug in rails when it generates the schema.rb file. It will not record the custom primary key state, you'll need to go in and edit it to add the id: :string option to the create_table call.

ie.

create_table "foo", id: :string, force: :cascade do |t|
  t.string   "name",       null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

The crappy part: that change will be undone every time you do a migration. You'd have to keep a close eye on the file content (or write a test :) ).

The bug is fixed in PR 18228, merged to rails:master on Jan 3, 2015.