Composite Primary Keys for ActiveRecords


ActiveRecords infamously doesn't support composite primary keys. This gem, composite_primary_keys, or CPK for short, extends ActiveRecord to support composite keys.


gem install composite_primary_keys

If you are using Rails add the following to your Gemfile:

gem 'composite_primary_keys', '=x.x.x' (see next section about what verison to use)


Every major version of ActiveRecord has included numerous internal changes. As a result, CPK has to be rewritten for each version of ActiveRecord. To help keep things straight, here is the mapping:

Version 7.x is designed to work with ActiveRecord 4.1.x
Version 6.x is designed to work with ActiveRecord 4.0.x
Version 5.x is designed to work with ActiveRecord 3.2.x
Version 4.x is designed to work with ActiveRecord 3.1.x

The basics

A model with composite primary keys is defined like this:

class Membership < ActiveRecord::Base
  self.primary_keys = :user_id, :group_id
  belongs_to :user
  belongs_to :group
  has_many :statuses, :class_name => 'MembershipStatus', :foreign_key => [:user_id, :group_id]

Note the addition of the line:

self.primary_keys = :user_id, :group_id

A model associated with a composite key model is defined like this:

class MembershipStatus < ActiveRecord::Base
  belongs_to :membership, :foreign_key => [:user_id, :group_id]

That is, associations can include composite keys too. All Rails association types are supported. Nice.


Once you’ve created your models to specify composite primary keys (such as the Membership class) and associations (such as MembershipStatus#membership), you can uses them like any normal model with associations.

But first, lets check out our primary keys.

MembershipStatus.primary_key # => "id"    # normal single key
Membership.primary_key  # => [:user_id, :group_id] # composite keys
Membership.primary_key.to_s # => "user_id,group_id"

Now we want to be able to find instances using the same syntax we always use for ActiveRecords…

MembershipStatus.find(1)    # single id returns single instance
=> <MembershipStatus:0x392a8c8 @attributes={"id"=>"1", "status"=>"Active"}>
Membership.find([1,1])  # composite ids returns single instance
=> <Membership:0x39218b0 @attributes={"user_id"=>"1", "group_id"=>"1"}>

Notice the use of an array to specify the composite key values.


CPK supports the following databases:

* PostgreSQL
* Oracle
* DB2
* SQLite
* SQLServer

Running Tests

See test/README_tests.rdoc

Questions, Discussion and Contributions

The CPK home page is hosted at There is also a Google group at!forum/compositekeys, but you'll have better luck getting support using GitHub.


First version was written by Dr Nic Williams.

Maintained by Charlie Savage

Contributions by many!