Rails, Creating Tables With Different Primary Keys (PostgreSQL) 03 Dec 2006
Say you’re using PostgreSQL and Rails and you want to create a table with a primary key that is not the field ‘id’. How do you make that work?

At first thought you might try the below:

ActiveRecord::Schema.create_table :articles, :id=>false do |t| t.column :title, :string end

You pass in :id=>false because that tells ActiveRecord that you don’t want ActiveRecord::Schema to create or set a field for your primary key. So you plug into your application code that actually relies on your new table:

class Article < ActiveRecord::Base

Article.create :title=>"Test Article"

You get an ActiveRecord::StatementInvalid error stating that relation “articles_id_seq” doesn’t exist? What gives?

The first problem is that since you told ActiveRecord::Schema not to create or set a primary key field, you don’t have one. ActiveRecord (and thus Rails) relies on having a primary key.

To fix this you’d simply modify your original code to look like:

ActiveRecord::Schema.create_table :articles, :id=>false, :primary_key=>'aid' do |t|
  t.column :aid, :serial
  t.column :title, :string

class Article < ActiveRecord::Base
  set_primary_key 'aid'

And now everything works!

You may notice that we use the column type serial instead of integer. This is because PostgreSQL is capable of handling auto incrementing integers and ActiveRecord expects it to unless you give it another way of handling unique primary keys (which is a topic for another post).

PostgreSQL does this with sequences, by using the serial (or even bigserial) column types you hint to PostgreSQL to automatically handle the creation of those sequences.

  • Click “here”:http://www.postgresql.org/docs/8.1/static/sql-createsequence.html for information sequences in PostgreSQL 8.1
  • Click “here”:http://www.postgresql.org/docs/8.1/static/datatype.html for information on data types in PostgreSQL 8.1

blog comments powered by Disqus