Composite primary keys in Rails

There are scenarios where a combination of columns needs to be used as the primary key. This is where composite primary keys come into play. This article explores composite primary keys, how they work in Rails, when they should be used, and what to consider when using them.

Primary keys are vital in the design of a relational database. They are crucial in uniquely identifying records.

For example, if you have a table of users, you will need to identify each record uniquely. This is where primary keys are used. Primary keys are usually single columns that auto-increment. There might be cases where you'll need a combination of column to serve as the primary key - this is where composite primary keys become helpful. This article will explore composite primary keys, how they work in Rails, when they should be used, and what to consider when using them.

Explanation of primary keys

Primary keys help ensure that records are uniquely identifiable. This ensures data integrity and efficient retrieval.

Rails, by default, will automatically create an integer column called id when you generate a new table using Rails migration. You can use the default primary key or a custom primary key (i.e., choose another column as your primary key).

class CreateProducts < ActiveRecord::Migration[7.1]
  def change
    create_table :products, id: false, primary_key: :slug do |t|
      t.string :title, null: false
      t.string :slug, null: false
      t.text :description

      t.index :slug, unique: true

In the above code, the migration will create a products table; the primary key for this table will be the slug column.

Next, we need to specify the primary key in the model:

class Product < ApplicationRecord
  self.primary_key = :slug

Now, we can search for the product using the following:


There are limitations when using single primary keys.

Let's say we are working on an e-commerce application with order and product tables. You could have a join table called order_products to ensure data integrity. This is because an order can have multiple products, and a product can belong to different orders. This table will hold the order_id and product_id. This is an instance of a many-to-many relationship.

Each row in the order_products table will represent a unique combination of an order and a product. This scenario is typical for using something different from a singular primary key.

What are composite primary keys?

Composite primary keys are composed of multiple columns - the combination of the values in these columns must be unique within the table. With it, you can identify a record using various attributes based on the columns' value.

For example, if you have a table for student enrollments and want to ensure that no student is enrolled in the same course more than once. You could use a composite primary key consisting of both the student ID and the course ID. With this, you ensure uniqueness by looking at both columns at once.

So, composite primary keys provide a way to express complex relationships and enforce unique identification across multiple columns. They're handy for organizing and managing data in databases.

How do they work?

With Rails 7.1, you no longer need to rely on the CPK gem, as Rails 7.1 now supports composite primary keys natively.

First, create your table:

class CreateOrderProducts < ActiveRecord::Migration[7.1]
  def change
    create_table :order_products, primary_key: [:order_id, :product_id] do |t|
      t.integer :order_id
      t.integer :product_id
      t.integer :quantity

In the above migrations, we set the primary_key to [:order_id, :product_id]. Now, whenever you want to fetch OrderProducts, you’ll need to do something like this: OrderProduct.find([1,9]). Then, we can specify the primary_key in the model like this:

class OrderProduct < ApplicationRecord
  self.primary_key = [:order_id, :product_id]

  belongs_to :order
  belongs_to :product

When should you use them?

Aside from many-to-many relationships, composite primary keys can be helpful when working on a multi-tenant application. Imagine you have a multi-tenant application where users and each user belongs to a particular organization. It’s possible to have a unique user_id within each organization, but the user_id itself might not be unique across the entire users table.

In this scenario, you could use a composite primary key of [:organisation_id, :user_id] to ensure that each user is uniquely identified across the entire application.

class CreateUsers < ActiveRecord::Migration[7.1]
  def change
    create_table :users, primary_key: [:organization_id, :user_id] do |t|
      t.integer :organization_id
      t.integer :user_id
      t.string :name
      t.string :email


While composite primary keys can be beneficial, there are some things you need to consider;

  1. Composite primary keys can increase the complexity of your database schema and application logic.
  2. Composite primary keys can result in performance degradation when inserting new records.
  3. Ensure that your database systems support composite primary keys and the necessary operations your application needs.

I suggest only using a composite primary key when necessary for your use case (i.e., when a single-column primary key won’t suffice).


In this article, we've looked at the significance of composite primary keys, how they work in Rails and when they should be used. We also looked at things to consider when using them. At this point, you should be able to make informed decisions during database designs. It's essential to remember the importance of data integrity and relationships.

The tool (single or composite primary keys) to use depends on your application's requirements and the sort of data you're working with. You need to approach your database design with the approach that best suits your needs.

What to do next:
  1. Try Honeybadger for FREE
    Honeybadger helps you find and fix errors before your users can even report them. Get set up in minutes and check monitoring off your to-do list.
    Start free trial
    Easy 5-minute setup — No credit card required
  2. Get the Honeybadger newsletter
    Each month we share news, best practices, and stories from the DevOps & monitoring community—exclusively for developers like you.
    author photo

    Kingsley Silas

    Kingsley works as a software engineer and enjoys writing technical articles.

    More articles by Kingsley Silas
    Stop wasting time manually checking logs for errors!

    Try the only application health monitoring tool that allows you to track application errors, uptime, and cron jobs in one simple platform.

    • Know when critical errors occur, and which customers are affected.
    • Respond instantly when your systems go down.
    • Improve the health of your systems over time.
    • Fix problems before your customers can report them!

    As developers ourselves, we hated wasting time tracking down errors—so we built the system we always wanted.

    Honeybadger tracks everything you need and nothing you don't, creating one simple solution to keep your application running and error free so you can do what you do best—release new code. Try it free and see for yourself.

    Start free trial
    Simple 5-minute setup — No credit card required

    Learn more

    "We've looked at a lot of error management systems. Honeybadger is head and shoulders above the rest and somehow gets better with every new release."
    — Michael Smith, Cofounder & CTO of YvesBlue

    Honeybadger is trusted by top companies like:

    “Everyone is in love with Honeybadger ... the UI is spot on.”
    Molly Struve, Sr. Site Reliability Engineer, Netflix
    Start free trial