Using Rails, how can I set my primary key to not be an integer-typed column?

Solution 1:

Unfortunately, I've determined it's not possible to do it without using execute.

Why it doesn't work

By examining the ActiveRecord source, we can find the code for create_table:

In schema_statements.rb:

def create_table(table_name, options={})
  ...
  table_definition.primary_key(options[:primary_key] || Base.get_primary_key(table_name.to_s.singularize)) unless options[:id] == false
  ...
end

So we can see that when you try to specify a primary key in the create_table options, it creates a primary key with that specified name (or, if none is specified, id). It does this by calling the same method you can use inside a table definition block: primary_key.

In schema_statements.rb:

def primary_key(name)
  column(name, :primary_key)
end

This just creates a column with the specified name of type :primary_key. This is set to the following in the standard database adapters:

PostgreSQL: "serial primary key"
MySQL: "int(11) DEFAULT NULL auto_increment PRIMARY KEY"
SQLite: "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL"

The workaround

Since we're stuck with these as the primary key types, we have to use execute to create a primary key that is not an integer (PostgreSQL's serial is an integer using a sequence):

create_table :employees, {:id => false} do |t|
  t.string :emp_id
  t.string :first_name
  t.string :last_name
end
execute "ALTER TABLE employees ADD PRIMARY KEY (emp_id);"

And as Sean McCleary mentioned, your ActiveRecord model should set the primary key using set_primary_key:

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

Solution 2:

This works:

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

It may not be pretty, but the end result is exactly what you want.

Solution 3:

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

Solution 4:

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.