Composite Primary Keys

1. What are Composite Primary Keys?

Composite primary keys (CPK) are designed to allow two or more columns to act as the unique identifier for a row in a database table. This is useful when a single column's value isn't enough to uniquely identify every row of a table. This can occur with legacy database schemas that lack a single id primary key, or in applications where the schema has been designed to partition data across multiple databases (sharding) or isolate data per customer or tenant (multitenancy). Composite primary keys is an Active Record feature that spans migrations, query methods, and associations.

For example, an inventory tracking application may have a table where each row is identified by a store_id and a product's sku (stock keeping unit) together but neither column alone identifies a row on its own. Active Record supports this by allowing you to declare [:store_id, :sku] as the primary key as detailed in a section below.

1.1. Using query_constraints as an Alternative

In cases where your table has a conventional id column but you want Active Record to scope queries using an additional column, you can use query_constraints instead of redefining the primary key entirely. For example:

class Developer < ActiveRecord::Base
  query_constraints :company_id, :id
end

This keeps id as the primary key at the database level while instructing Active Record to always include company_id in queries, updates, and deletes for Developers.

For example, given a developer record with company_id: 5:

irb> developer = Developer.find_by(company_id: 5, name: "Alice")
=> #<Developer id: 1, company_id: 5, name: "Alice">

With query_constraints, any subsequent query or update on that record will automatically include company_id as well:

irb> developer.update!(name: "Bob")

UPDATE developers SET name = 'Bob' WHERE company_id = 5 AND id = 1

Without query_constraints, that same update would only scope on id:

UPDATE developers SET name = 'Bob' WHERE id = 1

Query Constraints is a lighter weight option when you don't need a true composite primary key but want Rails to treat a combination of columns as the effective identity when querying.

2. Declaring Composite Primary Keys

To create a table with a composite primary key, you can pass an array to the primary_key: option in the database migration:

class CreateProducts < ActiveRecord::Migration[8.2]
  def change
    create_table :products, primary_key: [:store_id, :sku] do |t|
      t.references :store, null: false, foreign_key: true
      t.string :sku, null: false
      t.text :description
      t.timestamps
    end
  end
end

After running the migration above, your schema.rb file reflects the composite key:

# db/schema.rb
create_table "products", primary_key: [:store_id, :sku], force: :cascade do |t|
  t.bigint "store_id", null: false
  t.string "sku", null: false
  t.text "description"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

add_foreign_key "products", "stores", column: "store_id", primary_key: "id"

When using a composite primary key, uniqueness is enforced by the combination of columns rather than a single auto-incrementing id. In the above example, store_id is a foreign key to a stores table, and sku is a string (like "ABC-123"). Neither needs to be auto-generated, their combination is what's unique.

Rails detects the primary key automatically from the database at runtime. So as long as your migration has been run, your model will have the composite primary key.

Rails also supports declaring composite primary keys at the model level via self.primary_key method:

class Order < ApplicationRecord
  self.primary_key = [:store_id, :sku]
end

In most cases, you don't need to declare it manually, but self.primary_key can be useful if you're connecting to a legacy or external database where Rails fails to detect the composite primary key correctly, or if you need to override what the database reports for some other reason.

3. Querying Models

3.1. Using find

If your table uses a composite primary key, you'll need to pass an array when using find to locate a record. For example, to find the product with store_id 3 and sku "XYZ12345":

irb> product = Product.find([3, "XYZ12345"])
=> #<Product store_id: 3, sku: "XYZ12345", description: "Yellow socks">

The above Active Record method results in the following SQL:

SELECT * FROM products WHERE store_id = 3 AND sku = "XYZ12345"

To find multiple records with composite IDs, you can pass an array of arrays to find. For example, to find the products with primary keys [1, "ABC98765"] and [7, "ZZZ11111"]

irb> products = Product.find([[1, "ABC98765"], [7, "ZZZ11111"]])
=> [
  #<Product store_id: 1, sku: "ABC98765", description: "Red Hat">,
  #<Product store_id: 7, sku: "ZZZ11111", description: "Green Pants">
]

The above Active Record method results in the following SQL:

SELECT * FROM products WHERE ((store_id = 1 AND sku = 'ABC98765') OR (store_id = 7 AND sku = 'ZZZ11111'))

Models with composite primary keys will also use the full composite primary key when ordering:

irb> product = Product.first
=> #<Product store_id: 1, sku: "ABC98765", description: "Red Hat">

The above Active Record method results in the following SQL:

SELECT * FROM products ORDER BY products.store_id ASC, products.sku ASC LIMIT 1

Likewise, last will use the full composite primary key with the sort direction for each column reversed:

irb> product = Product.last
=> #<Product store_id: 7, sku: "ZZZ11111", description: "Green Pants">

The SQL equivalent of the above is:

SELECT * FROM products ORDER BY products.store_id DESC, products.sku DESC LIMIT 1

Here, the sort direction is descending for both store_id and sku because both columns are part of the composite primary key.

3.2. Using where

Hash conditions for where can query against multiple composite key values at once by passing an array of value pairs as well:

Product.where(Product.primary_key => [[1, "ABC98765"], [7, "ZZZ11111"]])

This returns all products matching either [store_id: 1, sku: "ABC98765"] or [store_id: 7, sku: "ZZZ11111"].

This generates the following SQL:

SELECT * FROM products WHERE (store_id = 1 AND sku = 'ABC98765' OR store_id = 7 AND sku = 'ZZZ11111')

4. Associations

Rails can generally infer the primary key to foreign key relationships between associated models. However, when using composite primary keys, Rails typically defaults to using only part of the composite key (usually the id column) unless explicitly instructed otherwise. This default behavior only works if the model's composite primary key contains the :id column and that column is unique for all records.

Consider the following example:

class Order < ApplicationRecord
  self.primary_key = [:store_id, :id]
  has_many :books
end

class Book < ApplicationRecord
  belongs_to :order
end

The composite primary key for Order is [:store_id, :id] but Rails will take just the :id column as the primary key for the association, making order_id the foreign key on Book.

Below we create an Order and a Book associated with it:

order = Order.create!(id: [1, 2], status: "pending")
book = order.books.create!(title: "A Cool Book")
book.reload.order

For the last line, Rails generates the following SQL to access the order:

SELECT * FROM orders WHERE id = 2

You can see that Rails uses the order's id in its query, rather than both the store_id and the id. In this case, the id is sufficient because the model's composite primary key does in fact contain the id column, and the column is unique for all records.

However, if the above requirement is not met, you can explicitly set the foreign_key option on the association. Then, all columns specified in the foreign key will be used when querying the associated records.

For example, consider an Author model whose composite primary key contains no :id column at all:

class Author < ApplicationRecord
  self.primary_key = [:first_name, :last_name]
  has_many :books, foreign_key: [:author_first_name, :author_last_name]
end

class Book < ApplicationRecord
  belongs_to :author, foreign_key: [:author_first_name, :author_last_name]
end

In this setup, Book belongs to Author and the a composite foreign key is specified as [:author_first_name, :author_last_name].

Create an Author and a Book associated with it:

author = Author.create!(first_name: "Jane", last_name: "Doe")
book = author.books.create!(title: "A Cool Book")
book.reload.author

Rails will now use both first_name and last_name from the composite primary key in the SQL query for Author:

SELECT * FROM authors WHERE first_name = 'Jane' AND last_name = 'Doe'

5. Forms and Controller Parameters

Forms may also be built for composite primary key models. See the Form Helpers guide for more information on the form builder syntax.

Given a @book model object with a composite key [:author_id, :id]:

@book = Book.find([2, 25])
# => #<Book id: 25, title: "Some book", author_id: 2>

The following form:

<%= form_with model: @book do |form| %>
  <%= form.text_field :title %>
  <%= form.submit %>
<% end %>

Outputs:

<form action="/books/2_25" method="post" accept-charset="UTF-8" >
  <input name="authenticity_token" type="hidden" value="..." />
  <input type="text" name="book[title]" id="book_title" value="My book" />
  <input type="submit" name="commit" value="Update Book" data-disable-with="Update Book">
</form>

Note the generated URL contains the author_id and id delimited by an underscore. Once submitted, the controller can extract primary key values from the parameters and update the record.

5.1. Extracting Composite Primary Key from params

Composite key parameters contain multiple values in one parameter so we need to extract each value and pass them to Active Record. We can use the extract_value method for this.

Given the following controller:

class BooksController < ApplicationController
  def show
    # Extract the composite ID value from URL parameters.
    id = params.extract_value(:id)
    # Find the book using the composite ID.
    @book = Book.find(id)
    # ...
  end
end

And the following route:

get "/books/:id", to: "books#show"

When a user opens the URL /books/4_2, the controller will extract the composite key value ["4", "2"] and pass it to Book.find to render the right record in the view. The extract_value method may be used to extract arrays out of any delimited parameters.

6. Fixtures

Fixtures for composite primary key tables are similar to normal tables. When using an id column, the column may be omitted as usual:

class Book < ApplicationRecord
  self.primary_key = [:author_id, :id]
  belongs_to :author
end

# books.yml
alices_adventure_in_wonderland:
  author_id: <%= ActiveRecord::FixtureSet.identify(:lewis_carroll) %>
  title: "Alice's Adventures in Wonderland"

However, in order to support composite primary key relationships, you must use the composite_identify method:

class BookOrder < ApplicationRecord
  self.primary_key = [:store_id, :id]
  belongs_to :order, foreign_key: [:store_id, :order_id]
  belongs_to :book, foreign_key: [:author_id, :book_id]
end

# book_orders.yml
alices_adventure_in_wonderland_in_books:
  author: lewis_carroll
  book_id: <%= ActiveRecord::FixtureSet.composite_identify(
              :alices_adventure_in_wonderland, Book.primary_key)[:id] %>
  shop: book_store
  order_id: <%= ActiveRecord::FixtureSet.composite_identify(
              :books, Order.primary_key)[:id] %>

7. Performance and Indexing

7.1. Column Order Matters

A composite primary key creates a database index on its columns in the order they are declared. A CPK of [:store_id, :sku] means the database can efficiently use that index for queries filtering on store_id alone, or store_id and sku together, but not sku alone. The leading column should be the one you filter on most frequently. In a multi-tenant application, placing the tenant identifier first (e.g. store_id) makes sense, since almost every query will be scoped to a store.

7.2. Index Foreign Key Columns Manually

When another table references a composite primary key, the foreign key columns on that table need their own index. Unlike single column foreign keys, Rails does not add these automatically. Without an explicit index, any join or association query back to the parent table will result in a full table scan.

You can add the index manually in your migration:

add_index :books, [:order_store_id, :order_sku]