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 found a solution to this that works with Rails 3:

The migration file:

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

And in the employee.rb model:

self.primary_key = :emp_id

Answer 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.

Answer 3

After nearly every solution which says "this worked for me on X database", I see a comment by the original poster to the effect of "didn't work for me on Postgres." The real issue here may in fact be the Postgres support in Rails, which is not flawless, and was probably worse back in 2009 when this question originally posted. For instance, if I remember correctly, if you're on Postgres, you basically can't get useful output from rake db:schema:dump.

I am not a Postgres ninja myself, I got this info from Xavier Shay's excellent PeepCode video on Postgres. That video actually overlooks a library by Aaron Patterson, I think Texticle but I could be remembering wrong. But other than that it's pretty great.

Anyway, if you're running into this problem on Postgres, see if the solutions work in other databases. Maybe use rails new to generate a new app as a sandbox, or just create something like

sandbox:
  adapter: sqlite3
  database: db/sandbox.sqlite3
  pool: 5
  timeout: 5000

in config/database.yml.

And if you can verify that it is a Postgres support issue, and you figure out a fix, please contribute patches to Rails or package your fixes in a gem, because the Postgres user base within the Rails community is pretty large, mainly thanks to Heroku.

Answer 4

How about this solution,

Inside Employee model why can't we add code that will check for uniqueness in coloumn, for ex: Assume Employee is Model in that you have EmpId which is string then for that we can add ":uniqueness => true" to EmpId

    class Employee < ActiveRecord::Base
      validates :EmpId , :uniqueness => true
    end

I am not sure that this is solution but this worked for me.

Answer 5

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

Answer 6

Adding index works for me, I'm using MySql btw.

create_table :cards, {:id => false} do |t|
    t.string :id, :limit => 36
    t.string :name
    t.string :details
    t.datetime :created_date
    t.datetime :modified_date
end
add_index :cards, :id, :unique => true