Getting empty migration when II try and create a unique index in my rails project

Question

I’m using Rails 4.2.3 with MySQL 5.5.37. I want to create a unique constraint on my table, involving multiple columns, so I ran …

rails generate migration add_index :user_objects, [:day, :object, :user], :unique => true

However, this produces a file, db/migrate/20160203003708_add_index.rb, which had nothing in it …

class AddIndex < ActiveRecord::Migration
  def change
  end
end

So obviously nothing happens when I run “rake db:migrate”. What I’m I doing wrong in my attempt to create a unique index across multiple columns and what’s the right way to do it on the command line?

Answer 1

According to the migrations guide and migrations API docs generating migrations from the command line only supports creating new tables and adding new columns. The table that needs to be created or column(s) added is determined from the name of the migration using the part after _to_. E.g. running a command like:

rails generate migration add_username_to_user_objects username:string:uniq

would generate a migration which adds a unique usernamecolumn (even if it already exists) to the user_objects table. Now, even if the command supported adding indexes etc. the syntax would probably be so hard to remember and fragile (because of Bash etc) that many would prefer just writing the code into the file anyway :) :

# db/migrate/20160203003708_add_index.rb
def change
    add_index :user_objects, [:day, :object, :user], unique: true
end

---

A suggestion: The order of fields in a unique index affects performance and some other rules regarding SQL. E.g. if user is a FK to users table, putting it first would cover queries where you use user in a WHERE, ORDER, GROUP BY etc (e.g. SELECT ... FROM user_objects WHERE user_id = ...) without needing a separate index for it. But a more important factor is cardinality, which is basically "How unique is each of day, object, userin user_objects?" Put the most unique first.