1. What are Composite Primary Keys?

Sometimes a single column's value isn't enough to uniquely identify every rowof a table, and a combination of two or more columns is required.This can be the case when using a legacy database schema without a singleidcolumn as a primary key, or when altering schemas for sharding or multitenancy.

Composite primary keys increase complexity and can be slower than a singleprimary key column. Ensure your use-case requires a composite primary keybefore using one.

2. Composite Primary Key Migrations

You can create a table with a composite primary key by passing the:primary_key option tocreate_table with an array value:

classCreateProducts<ActiveRecord::Migration[8.1]defchangecreate_table:products,primary_key:[:store_id,:sku]do|t|t.integer:store_idt.string:skut.text:descriptionendendend

3. Querying Models

3.1. Using#find

If your table uses a composite primary key, you'll need to pass an arraywhen using#find to locate a record:

# 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 SQL equivalent of the above is:

SELECT*FROMproductsWHEREstore_id=3ANDsku="XYZ12345"

To find multiple records with composite IDs, pass an array of arrays to#find:

# 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 SQL equivalent of the above is:

SELECT*FROMproductsWHERE(store_id=1ANDsku='ABC98765'ORstore_id=7ANDsku='ZZZ11111')

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

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

The SQL equivalent of the above is:

SELECT*FROMproductsORDERBYproducts.store_idASC,products.skuASCLIMIT1

3.2. Using#where

Hash conditions for#where may be specified in a tuple-like syntax.This can be useful for querying composite primary key relations:

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

3.2.1. Conditions with:id

When specifying conditions on methods likefind_by andwhere, the useofid will match against an:id attribute on the model. This is differentfromfind, where the ID passed in should be a primary key value.

Take caution when usingfind_by(id:) on models where:id is not the primarykey, such as composite primary key models. See theActive Record Queryingguide to learn more.

4. Associations between Models with Composite Primary Keys

Rails can often infer the primary key-foreign key relationships betweenassociated models. However, when dealing with composite primary keys, Railstypically defaults to using only part of the composite key, usually theidcolumn, unless explicitly instructed otherwise. This default behavior only worksif the model's composite primary key contains the:id column,and the columnis unique for all records.

Consider the following example:

classOrder<ApplicationRecordself.primary_key=[:shop_id,:id]has_many:booksendclassBook<ApplicationRecordbelongs_to:orderend

In this setup,Order has a composite primary key consisting of[:shop_id,:id], andBook belongs toOrder. Rails will assume that the:id columnshould be used as the primary key for the association between an order and itsbooks. It will infer that the foreign key column on the books table is:order_id.

Below we create anOrder and aBook associated with it:

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

To access the book's order, we reload the association:

book.reload.order

When doing so, Rails will generate the following SQL to access the order:

SELECT*FROMordersWHEREid=2

You can see that Rails uses the order'sid in its query, rather than both theshop_id and theid. In this case, theid is sufficient because the model'scomposite primary key does in fact contain the:id column,and the column isunique for all records.

However, if the above requirements are not met or you would like to use the fullcomposite primary key in associations, you can set theforeign_key: option onthe association. This option specifies a composite foreign key on theassociation; all columns in the foreign key will be used when querying theassociated record(s). For example:

classAuthor<ApplicationRecordself.primary_key=[:first_name,:last_name]has_many:books,foreign_key:[:first_name,:last_name]endclassBook<ApplicationRecordbelongs_to:author,foreign_key:[:author_first_name,:author_last_name]end

In this setup,Author has a composite primary key consisting of[:first_name,:last_name], andBook belongs toAuthor with a composite foreign key[:author_first_name, :author_last_name].

Create anAuthor and aBook associated with it:

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

To access the book's author, we reload the association:

book.reload.author

Rails will now use the:first_nameand:last_name from the compositeprimary key in the SQL query:

SELECT*FROMauthorsWHEREfirst_name='Jane'ANDlast_name='Doe'

5. Forms for Composite Primary Key Models

Forms may also be built for composite primary key models.See theForm 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_withmodel:@bookdo|form|%><%=form.text_field:title%><%=form.submit%><%end%>

Outputs:

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

Note the generated URL contains theauthor_id andid delimited by anunderscore. Once submitted, the controller can extract primary key values fromthe parameters and update the record. See the next section for more details.

6. Composite Key Parameters

Composite key parameters contain multiple values in one parameter.For this reason, we need to be able to extract each value and pass them toActive Record. We can leverage theextract_value method for this use-case.

Given the following controller:

classBooksController<ApplicationControllerdefshow# Extract the composite ID value from URL parameters.id=params.extract_value(:id)# Find the book using the composite ID.@book=Book.find(id)# use the default rendering behavior to render the show view.endend

And the following route:

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

When a user opens the URL/books/4_2, the controller will extract thecomposite key value["4", "2"] and pass it toBook.find to render the rightrecord in the view. Theextract_value method may be used to extract arraysout of any delimited parameters.

7. Composite Primary Key Fixtures

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

classBook<ApplicationRecordself.primary_key=[:author_id,:id]belongs_to:authorend

# books.ymlalices_adventure_in_wonderland:author_id:<%= ActiveRecord::FixtureSet.identify(:lewis_carroll) %>title:"Alice'sAdventuresinWonderland"

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

classBookOrder<ApplicationRecordself.primary_key=[:shop_id,:id]belongs_to:order,foreign_key:[:shop_id,:order_id]belongs_to:book,foreign_key:[:author_id,:book_id]end

# book_orders.ymlalices_adventure_in_wonderland_in_books:author:lewis_carrollbook_id:<%= ActiveRecord::FixtureSet.composite_identify(:alices_adventure_in_wonderland, Book.primary_key)[:id] %>shop:book_storeorder_id:<%= ActiveRecord::FixtureSet.composite_identify(:books, Order.primary_key)[:id] %>