Rails Composite Primary Keys: CPK, Legacy Schemas, and Natural Keys in ActiveRecord

The schema was a gift from a decade-old Oracle migration. Every table had a company_id column and an id that was only unique within a company. The application that produced the schema had never heard of Rails, so when the client wanted to rebuild on Rails — “Rails handles everything, right?” — the problem landed in my lap.

Before Rails 7.1, the answer was painful: a surrogate auto-increment primary key bolted onto every table, a custom find override to avoid single-column lookups, and endless where(company_id: current_company.id) guards that junior developers kept forgetting. Seventeen months in, we found a query path in the billing module that forgot the guard. We refunded sixty-two customers who had seen each other’s invoices.

Rails composite primary keys, shipped natively in 7.1, solve this class of problem at the model layer. Here is what they are, how they work, and the edges that will catch you if nobody warns you first.

What Are Rails Composite Primary Keys?

A composite primary key is a primary key consisting of two or more columns. Instead of a single id that uniquely identifies a row anywhere in the table, the database enforces uniqueness on the combination of values. The canonical examples:

• (shop_id, order_id) — orders scoped to a shop in a multi-tenant system
• (user_id, post_id) — a join table that is also the canonical record
• (event_date, event_id) — partitioned tables where id resets per partition
• (account_id, id) — a legacy Oracle schema where id is company-scoped

Rails has always been able to talk to such tables via raw SQL, but every convenience method — Model.find, belongs_to, has_many, URL helpers — assumed a single primary key column named id. Rails 7.1 ended that assumption cleanly.

Declaring Composite Primary Keys in Rails

The declaration is a one-liner on the model:

class Order < ApplicationRecord
  self.primary_key = [:shop_id, :id]
end

The order matters. Rails uses the array as an ordered tuple; [:shop_id, :id] and [:id, :shop_id] produce different SQL when you call find. Match the order to how you want to look things up.

In a migration, declare the composite primary key explicitly:

class CreateOrders < ActiveRecord::Migration[7.1]
  def change
    create_table :orders, primary_key: [:shop_id, :id] do |t|
      t.bigint :shop_id, null: false
      t.bigint :id, null: false
      t.string :status, null: false
      t.timestamps
    end
  end
end

If id still needs auto-increment behaviour on the second column, PostgreSQL handles this with a sequence that is independent of the primary key constraint:

CREATE SEQUENCE orders_id_seq;

CREATE TABLE orders (
  shop_id bigint NOT NULL,
  id      bigint NOT NULL DEFAULT nextval('orders_id_seq'),
  status  varchar NOT NULL,
  created_at timestamp NOT NULL,
  updated_at timestamp NOT NULL,
  PRIMARY KEY (shop_id, id)
);

You can call execute from a migration for this. For most applications, keeping id as a global auto-increment and declaring (tenant_id, id) as the composite primary key is the cleanest path: the composite key enforces the relational invariant, and id retains a global uniqueness property you can use when you have authorization context.

find, find_by, and where with Composite Primary Keys

Model.find with a composite primary key takes an array:

Order.find([42, 1001])  # shop_id: 42, id: 1001

This generates:

SELECT * FROM orders WHERE shop_id = 42 AND id = 1001 LIMIT 1

Finding multiple records:

Order.find([[42, 1001], [42, 1002], [42, 1003]])

Rails uses tuple comparison syntax here — WHERE (shop_id, id) IN ((42, 1001), (42, 1002), (42, 1003)) — which PostgreSQL handles efficiently with a composite index.

find_by works on named attributes as always:

Order.find_by(shop_id: 42, id: 1001)

where is unchanged:

Order.where(shop_id: 42).order(:id)

The change you will notice immediately: Order.find(1001) raises ActiveRecord::StatementInvalid once you declare a composite primary key. Single-value find is gone. This is a breaking change in existing code and the main thing to audit during a migration.

Associations with Composite Primary Keys

Associations are where Rails CPK support gets sophisticated — and where you will spend most of your debugging time.

A has_many through a composite primary key needs the foreign key spelled out:

class Shop < ApplicationRecord
  has_many :orders, foreign_key: :shop_id
end

class Order < ApplicationRecord
  self.primary_key = [:shop_id, :id]
  belongs_to :shop
end

belongs_to can often infer from the primary key declaration, but being explicit removes ambiguity. When the foreign key on the child is itself composite — the association spans two columns — use the query_constraints option:

class OrderLine < ApplicationRecord
  self.primary_key = [:shop_id, :order_id, :position]

  belongs_to :order, query_constraints: [:shop_id, :order_id]
end

query_constraints tells Rails which columns participate in the association join, not just which column is the foreign key. Without it, Rails joins only on order_id, which is not unique without shop_id.

Verify your associations generate correct SQL before shipping:

shop = Shop.find(42)
shop.orders.to_sql
# => SELECT * FROM orders WHERE orders.shop_id = 42

If you see a narrower condition than expected, or a missing component of the composite key in the WHERE clause, query_constraints is missing or misspecified.

Routes and URL Helpers with Composite Primary Keys

Rails URL helpers call to_param on your model. By default with CPK, to_param returns the composite key values joined with an underscore:

order = Order.find([42, 1001])
order.to_param  # => "42_1001"

This generates URLs like /orders/42_1001. That works but reads poorly. Two patterns I prefer:

Pattern 1: Nested resources

resources :shops do
  resources :orders, only: [:show, :edit, :update, :destroy]
end

shop_order_path(shop, order) takes two separate parameters and the controller receives params[:shop_id] and params[:id] cleanly. The composite key never appears in the URL, and the parent scoping is explicit in the routes file.

Pattern 2: Override to_param

class Order < ApplicationRecord
  self.primary_key = [:shop_id, :id]

  def to_param
    id.to_s
  end
end

If id is globally unique — auto-increment from a global sequence — this is safe. Authorization happens in the controller from the session or token, not the URL. This is the pattern most teams were already using before CPK existed; the CPK declaration now gives you the database-level invariant without requiring you to surface both key components in URLs.

Legacy Schema Migration: The Real-World Case

The most common reason teams reach for Rails composite primary keys is a legacy schema they did not design. The migration path from a surrogate key retrofit to a native CPK model:

Before (the surrogate key workaround):

class Invoice < ApplicationRecord
  # :id is an auto-increment surrogate added at migration time
  # natural key: [:company_id, :number]
  # :number is only unique within a company — plain find(id) loses the scoping
end

After (native CPK):

class Invoice < ApplicationRecord
  self.primary_key = [:company_id, :number]
end

The migration to drop the surrogate key needs careful sequencing. Following a zero-downtime migration strategy, the steps are:

1. Add a UNIQUE constraint on (company_id, number) in a separate migration. This enforces the invariant at the database level before you touch any application code.
2. Deploy the model change (self.primary_key = [:company_id, :number]) behind a flag. Verify that find paths generate correct SQL in staging.
3. Audit every call site that uses Invoice.find(id) — grep -rn 'Invoice\.find(' app/ is your starting point. Update them to Invoice.find([company_id, number]) or reroute through find_by.
4. Drop the surrogate id column and its index in a final migration after a full deployment cycle confirms nothing depends on it.

strong_migrations catches the column removal as potentially unsafe — use safety_assured only after you have confirmed the column is not referenced by any index, constraint, or application code path.

For a large table, ALTER TABLE ... DROP COLUMN id in PostgreSQL is a metadata-only operation on heap tables (since PG 9.x), so the ACCESS EXCLUSIVE lock is brief. For billion-row tables, coordinate with your DBA and prefer a low-traffic window regardless.

Composite Primary Keys and Multi-Tenancy

The scenario from the introduction — company_id-scoped IDs in a legacy Oracle schema — maps directly to composite primary keys. But CPK does not replace application-level scoping; it complements it.

With (company_id, id) as the primary key, Order.find([42, 1001]) raises RecordNotFound if that combination does not exist. This is a useful invariant. It does not prevent Order.find([99, 1001]) from returning a different company’s order if the caller controls company_id. In a web application where company_id comes from the session, the composite key is fine. In an API where company_id could be a request parameter, you still need explicit scoping.

If you are building multi-tenant Rails architecture, treat CPK as a structural invariant — the database cannot create orphaned or incorrectly keyed rows — rather than an access control mechanism. The application must still enforce who can look up what.

The cleanest pattern is a scoped find through the association:

class Shop < ApplicationRecord
  has_many :orders, foreign_key: :shop_id
end

class Order < ApplicationRecord
  self.primary_key = [:shop_id, :id]
  belongs_to :shop
end

# In the controller — always find through the association
@order = current_shop.orders.find(params[:id])

current_shop.orders.find(params[:id]) applies the association’s WHERE clause before the primary key condition. Even if a client sends a different shop_id, the association scope limits results to current_shop. The composite primary key then provides a second layer of structural certainty.

Performance Considerations

A composite primary key is a B-tree index on multiple columns, in declaration order. Queries that filter on (shop_id, id) use the index fully. Queries that filter only on id cannot use the composite primary key index — they need a separate index on id if you look up by that column alone.

# Add a supporting index if you need fast lookups by id alone
add_index :orders, :id

For partitioned tables where id resets per partition, Postgres table partitioning enforces uniqueness per partition, not globally. A composite primary key that includes the partition key — (event_date, id) — gives you locally unique constraints on each partition. Global uniqueness across all partitions requires a global sequence for id, which you can set up independently of the composite PK constraint.

The write path has no overhead over a single-column primary key. The composite primary key is enforced at the B-tree level, identical to how a single-column PK is enforced, just on more columns. The index is slightly larger in bytes, but not meaningfully so for typical column types.

Testing Composite Primary Keys

Standard Minitest and RSpec patterns work with minor adjustments:

# Minitest
test "finds order by composite key" do
  order = Order.create!(shop_id: 1, status: "pending")
  found = Order.find([1, order.id])
  assert_equal order, found
end

test "scoped find raises on wrong shop" do
  other_shop = shops(:other)
  order = Order.create!(shop_id: 1, status: "pending")
  assert_raises(ActiveRecord::RecordNotFound) do
    other_shop.orders.find(order.id)
  end
end

In FactoryBot, ensure shop_id is never accidentally omitted — with a composite primary key, the database rejects a row missing a NOT NULL component:

FactoryBot.define do
  factory :order do
    association :shop
    shop_id { shop.id }
    status  { "pending" }
  end
end

A common trap: using Order.create! in a test without specifying shop_id when the column is NOT NULL and part of the primary key. The PostgreSQL error message is clear, but it is easy to overlook in test setup when you are used to single-column PKs that hide behind auto-increment defaults.

For system tests and request specs, the composite key does not change anything at the HTTP layer as long as you have sensible routing — the controller receives named parameters regardless of the underlying key structure.

When Not to Use Composite Primary Keys

When you have a clean schema from the start. If you control the schema and there is no legacy constraint forcing two-column uniqueness, a single auto-increment id plus a composite unique index on the natural key is simpler. Rails has over a decade of convention built around single-column primary keys; fight convention only when you have a genuine reason to.

When you need globally portable references. UUIDs or auto-increment integers can be handed to external systems — webhooks, emails, third-party APIs — without context. A composite key like [shop_id, 1001] requires both pieces to dereference; if a consumer loses shop_id, the reference breaks.

When gem compatibility is uncertain. Some gems assume a single id primary key — ActiveAdmin, some Devise token paths, older serializers. Rails 7.1+ has improved interoperability significantly, but check your dependency list against known CPK compatibility before committing. Running grep -rn 'primary_key\|\.id\b' vendor/bundle/ruby on your specific gems is tedious but informative.

When the team cost outweighs the structural benefit. A team of three where most members joined six months ago will lose more time to unexpected CPK edge cases than they gain from the structural guarantee. Evaluate the complexity cost honestly. Composite primary keys solve a real class of problem; they are not always the right tool.

Frequently Asked Questions

How do I use Rails composite primary keys with find_or_create_by?

Pass all primary key components as keyword arguments:

Order.find_or_create_by(shop_id: 42, id: 1001) do |order|
  order.status = "pending"
end

If you pass only one component of the composite key, Rails generates a query that may match multiple rows or none. Always include all CPK columns in find_or_create_by calls.

Can Rails composite primary keys work with Devise?

Devise assumes a single-column primary key named id for session tokens and authentication helpers. Running Devise on a composite primary key model requires significant patching of internal Devise methods. The standard solution is to leave the User model with a single id primary key and use composite PKs only on domain tables — orders, events, join tables — where the legacy or structural constraint actually lives.

Do composite primary keys work with Rails strong_migrations?

Yes. strong_migrations treats composite primary key tables like any other table — it validates index creation, column removal, and type changes against its safety checklist. Declare the composite primary key in the initial create_table call rather than with a subsequent ADD CONSTRAINT to avoid the lock issues that come with altering an existing primary key on a live table.

How do composite primary keys affect ActiveRecord’s query cache?

ActiveRecord’s query cache and in-memory identity map key objects by their primary key value. With CPK, the cache key is the array [shop_id, id]. Two records with different shop_id but the same id are correctly identified as distinct — this is an improvement over the single-column case, where unscoped and association-scoped queries on the same id could theoretically collide in the cache if you were not careful.

Dealing with a legacy schema that does not fit Rails conventions, or planning a Rails 7.1+ upgrade and wondering which battles are worth fighting? TTB Software does this work. Nineteen years of Rails means we know which migrations earn their complexity and which ones are better left alone.