Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Altering the primary key in Rails to be a string

So I've got two models, State and Acquisition. State has_many Acquisitions. I felt like an autoincrementing integer primary key for 51 records was rather silly. So I altered the model for the State to be the PK (State being the two letter abbreviation; I'm not storing the actual state name anywhere:

class State < ActiveRecord::Base     self.primary_key = "state"     has_many :acquisition_histories   end 

The problem is when I created my Acquisition model, it created the foreign key column state_id as an integer. More specifically, the script/generated migration did:

class CreateAcquisitions < ActiveRecord::Migration     def self.up       create_table :acquisitions do |t|         t.date :date         t.string :category         t.text :notes         t.references :state         t.timestamps       end   end end 

I'm assuming that t.references data type sets it to int. The problem is my create method on my Acquisition class is trying to put a state abbreviation into the state_id field on the table acquisitions (and yes, it's called state_id on the database, even though it says :state in the migration script). The method doesn't fail, but it does put a 0 in the state_id field and the records go into the ether.

like image 892
fr0man Avatar asked Apr 15 '09 05:04

fr0man


People also ask

Can primary keys be strings?

Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you're making it for and the length of the string that is going to be the primary key (longer strings == harder to compare).

What is best way to create primary key as a string field instead of integer in rails?

So: use :id => false so as not to generate an integer primary key. use the desired datatype, and add :null => false.

Is string as primary key bad?

Yes, from a performance standpoint (i.e. inserting or querying or updating) using Strings for primary keys are slower than integers. But if it makes sense to use string for the primary key then you should probably use it.


2 Answers

Though, I agree that this might be more trouble than it's worth considering the extra effort of working against the defaults elsewhere, just in case you actually want to do what you've asked:

Create states migration:

class CreateStatesTable < ActiveRecord::Migration     def change     create_table :states, id: false do |t|       t.string :state, limit: 2       t.string :name       t.index :state, unique: true     end   end end 

states model:

class State < ActiveRecord::Base   self.primary_key = :state end 

Note that before Rails 3.2, this was set_primary_key = :state instead of self.primary_key= see: http://guides.rubyonrails.org/3_2_release_notes.html#active-record-deprecations

like image 78
mkirk Avatar answered Oct 11 '22 19:10

mkirk


if you find yourself here... leave as quickly as you can and go to: Using Rails, how can I set my primary key to not be an integer-typed column?

like image 37
Wilhelm Avatar answered Oct 11 '22 21:10

Wilhelm