Rails, Creating Tables With Different Primary Keys (PostgreSQL)

on December 03, 2006 @ 03:48 AM

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:

1
2
3

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:

1
2
3
4

class Article < ActiveRecord::Base
end

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:

1
2
3
4
5
6
7
8

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

class Article < ActiveRecord::Base
  set_primary_key 'aid'
end

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 for information sequences in PostgreSQL 8.1
• Click here for information on data types in PostgreSQL 8.1