Using Rails, how can I set my primary key so that it is not an integer type column?

Question

I use Rails migrations to manage the database schema, and I create a simple table where I would like to use a non-integer value as the primary key (in particular, the row). To distract from my problem, let's say there is a employees table where employees are identified by an alphanumeric string, for example. "134SNW" .

I tried to create a table in migration as follows:

 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 seems to have completely ignored the t.string :emp_id and went ahead and made it an entire column. Is there any other way for the rails to generate a PRIMARY_KEY constraint (I use PostgreSQL) for me, without having to write SQL in the execute call?

NOTE I know that it is not better to use string columns as primary keys, so please do not respond simply by adding an integer primary key. I can add it anyway, but this question is still valid.

Answer 1

Unfortunately, I decided that this cannot be done without using execute .

Why it does not work

After examining the source of ActiveRecord, 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 see that when we try to specify the primary key in the create_table parameters, it creates a primary key with the specified name (or, if none is specified, id ). He does this by calling the same method that you can use inside the table definition block: primary_key .

In schema_statements.rb :

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

This simply creates a column with the specified type name :primary_key . The following are installed on standard database adapters:

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

Workaround

Since we are obsessed with them as primary key types, we must use execute to create a primary key that is not an integer (PostgreSQL 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 McCleery noted , your ActiveRecord model should set the primary key using set_primary_key :

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

Answer 2

I have one way to handle this. Run SQL is ANSI SQL, so it will most likely work with most ANSI SQL compatible relational databases. I 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 the following:

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

Answer 3

It 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 very pretty, but the end result is exactly what you want.

Answer 4

It seems like you can use 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 

This will cause column widget_id to become the main key for the Widget class, then you need to fill in the field when creating objects. You should be able to do this using a query before creating.

So, something like strings

 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 5

I am on Rails 2.3.5 and my next way is working with SQLite3

 create_table :widgets, { :primary_key => :widget_id } do |t| t.string :widget_id # other column definitions end 

No need: id => false.

Answer 6

I tried this 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 viewing the documentation column(name, type, options = {}) and read the line:

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

I got the above as I showed. The following is the table metadata after performing 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 the Rails console:

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

Answer 7

After almost every decision that says “it worked for me in the X database,” I see a comment on the original poster that “didn't work for me in Postgres.” The real issue here might be Postgres support in Rails, which is not perfect and was probably worse in 2009 when this question was originally posted. For example, if I remember correctly if you are on Postgres, you basically cannot get useful output from rake db:schema:dump .

I am not a Postgres ninja myself, I got this information from Xavier Shay on a great Postgres PeepCode video. This video actually skips Aaron Patterson’s library, I think the Texticle, but I might remember wrong. But other than that, it's pretty good.

Anyway, if you run into this problem in Postgres, see if the solutions work in other databases. Maybe use rails new to create a new sandboxed application, 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 this is a problem with Postgres support, and you figured out a fix, make corrections in Rails or package fixes in gem, because the Postgres user base in the Rails community is quite large, mainly thanks to Gerok.

Answer 8

I found a solution for this that works with Rails 3:

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 employee.rb model:

 self.primary_key = :emp_id 

Answer 9

In Rails 5 you can do

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

See the create_table documentation .

Answer 10

you need to use the option: id => false

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

Answer 11

The trick that worked for me on Rails 3 and MySQL was as follows:

 create_table :events, {:id => false} do |t| t.string :id, :null => false end add_index :events, :id, :unique => true 

So:

• use: id => false so as not to generate an integer primary key
• use the desired data type and add: null => false
• add a unique index to this column

MySQL seems to convert a unique index in a non null column to a primary key!

Answer 12

How about this solution,

Inside the Employee model, why can't we add code that will check the uniqueness in coloumn, for example: Suppose Employee is a Model, since you have EmpId, which is a string, for which we can add ": uniqueness => true" for EmpId

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

I'm not sure if this is a solution, but it worked for me.

Answer 13

I know this is an old thread I stumbled upon ... but I'm in shock, no one mentioned DataMapper.

I believe that if you need to deviate from the ActiveRecord agreement, I have found this to be a great alternative. Also, this is the best approach to heritage, and you can maintain the database “as is”.

The Ruby Object Mapper (DataMapper 2) contains many promises and is based on AREL principles too!

Answer 14

Adding an index works for me, I use 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