CODEWITHSUNDEEP
ruby-on-rails
Daniel Viglione
Daniel Viglione

Reputation: 9407

Join tables in ActiveRecord Migrations

I was reading the Rails Guides and discovered a new syntax for creating join tables:

class CreateJoinTableCustomerProduct < ActiveRecord::Migration[5.0]
  def change
    create_join_table :customers, :products do |t|
      # t.index [:customer_id, :product_id]
      # t.index [:product_id, :customer_id]
    end
  end
end

Perhaps this helper method create_join_table is new in Rails 5 and generates the appropriate join table in schema.rb. But it is this part that perturbed me:

  # t.index [:customer_id, :product_id]
  # t.index [:product_id, :customer_id]

Typically, if you want to add an index to a column, you do something like this:

add_index :products, :product_id

But why in this migration are there two indexes, with the same two columns? What is the explanation of this syntax?

Upvotes: 1

Views: 321

Answers (1)

max
max

Reputation: 101811

Database indexes are not limited to a single column.

t.index [:customer_id, :product_id]

Passing an array creates a compound index which indexes the combination of two columns - which is exactly what a join table is.

This can for example be used to enforce the uniqueness of a combination of values:

t.index [:user_id, :coupon_id], unique: true

Or just speed up queries.

The reason Rails creates two seperate indexes:

# t.index [:customer_id, :product_id]
# t.index [:product_id, :customer_id]

Is that the order actually matters for performance - a grossly simplefied rule of thumb for b-tree indexes is that you should place the most selective column first. You are supposed to choose the best compound index for your use case.

See:

Upvotes: 2

Related Questions