Ruby on Rails - Migrations, unique columns and indexing (using arrays)

Question

This question has been asked here, but an answer gave me a follow-up question.

How do I make a column unique and index it in a Ruby on Rails migration?

add_index :table_name, :column_name, :unique => true

To index multiple columns together, you pass an array of column names instead of a single column nam

add_index :table_name, [:column_name_a, :column_name_b]

I was browsing some code on the web and came across this snippet.

class AddIndexesToSyllabus < ActiveRecord::Migration
  def change
    add_index :syllabuses, :template_id, unique: true
    add_index :syllabuses, :edit_id, unique: true
    add_index :syllabuses, :view_id, unique: true
  end
end

I had no idea what unique: true did so I searched and came across the aforementioned link. However, I must ask this question. Is it possible to re-arrange that code like this.

class AddIndexesToSyllabus < ActiveRecord::Migration
  def change
    add_index :syllabuses, [:template_id, edit_id, view_id], unique: true
  end
end

In the above example, we have the table name, syllabuses along with three column-names(template_id, edit_id and view_id). The previous question led me to believe that the above is possible, but there was no confirmation. I'm trying to learn how to write efficient code and my suggestion is a lot more efficient. Would this work?

Thanks for your help.

score 1 · Accepted Answer · answered Jul 17 '14 at 23:17

You can create unique indexes either way, but the two methods are not equivalent.

Defining the multi-column index will ensure uniqueness of the combination of columns, so these combinations would all be valid:

+-------------+---------+---------+
| template_id | edit_id | view_id |
+-------------+---------+---------+
|           1 |       1 |       1 |
+-------------+---------+---------+
|           1 |       2 |       1 |
+-------------+---------+---------+
|           2 |       1 |       1 |
+-------------+---------+---------+

Whereas with the individual indexes, all but the first combination would fail.

Ruby on Rails - Migrations, unique columns and indexing (using arrays)

1 Answers1