How do I handle too long index names in a Ruby on Rails ActiveRecord migration?

Question

I am trying to add a unique index that gets created from the foreign keys of four associated tables:

add_index :studies,
  ["user_id", "university_id", "subject_name_id", "subject_type_id"],
  :unique => true

The database’s limitation for the index name causes the migration to fail. Here’s the error message:

Index name 'index_studies_on_user_id_and_university_id_and_subject_name_id_and_subject_type_id' on table 'studies' is too long; the limit is 64 characters

How can I handle this? Can I specify a different index name?

Answer 1

Provide the :name option to add_index, e.g.:

add_index :studies,
  ["user_id", "university_id", "subject_name_id", "subject_type_id"], 
  unique: true,
  name: 'my_index'

If using the :index option on references in a create_table block, it takes the same options hash as add_index as its value:

t.references :long_name, index: { name: :my_index }

Answer 2

You can change the index name in column definitions within a create_table block (such as you get from the migration generator).

create_table :studies do |t|
  t.references :user, index: {:name => "index_my_shorter_name"}
end

Answer 3

In PostgreSQL, the default limit is 63 characters. Because index names must be unique it's nice to have a little convention. I use (I tweaked the example to explain more complex constructions):

def change
  add_index :studies, [:professor_id, :user_id], name: :idx_study_professor_user
end

The normal index would have been:

:index_studies_on_professor_id_and_user_id

The logic would be:

• index becomes idx
• Singular table name
• No joining words
• No _id
• Alphabetical order

Which usually does the job.

Answer 4

You can also do

t.index([:branch_id, :party_id], unique: true, name: 'by_branch_party')

as in the Ruby on Rails API.

Answer 5

Similar to the previous answer: Just use the 'name' key with your regular add_index line:

def change
  add_index :studies, :user_id, name: 'my_index'
end

Answer 6

I'm afraid none of these solutions worked for me. Perhaps because I was using belongs_to in my create_table migration for a polymorphic association.

I'll add my code below and a link to the solution that helped me in case anyone else stumbles upon when searching for 'Index name is too long' in connection with polymorphic associations.

The following code did NOT work for me:

def change
  create_table :item_references do |t|
    t.text :item_unique_id
    t.belongs_to :referenceable, polymorphic: true
    t.timestamps
  end
  add_index :item_references, [:referenceable_id, :referenceable_type], name: 'idx_item_refs'
end

This code DID work for me:

def change
  create_table :item_references do |t|
    t.text :item_unique_id
    t.belongs_to :referenceable, polymorphic: true, index: { name: 'idx_item_refs' }

    t.timestamps
  end
end

This is the SO Q&A that helped me out: https://stackoverflow.com/a/30366460/3258059

Answer 7

I have a project that uses generators a lot and needed this to be automatic, so I copied the index_name function from the rails source to override it. I added this in config/initializers/generated_index_name.rb:

# make indexes shorter for postgres
require "active_record/connection_adapters/abstract/schema_statements"
module ActiveRecord
  module ConnectionAdapters # :nodoc:
    module SchemaStatements
      def index_name(table_name, options) #:nodoc:
        if Hash === options
          if options[:column]
            "ix_#{table_name}_on_#{Array(options[:column]) * '__'}".slice(0,63)
          elsif options[:name]
            options[:name]
          else
            raise ArgumentError, "You must specify the index name"
          end
        else
          index_name(table_name, index_name_options(options))
        end
      end
    end
  end
end

It creates indexes like ix_assignments_on_case_id__project_id and just truncates it to 63 characters if it's still too long. That's still going to be non-unique if the table name is very long, but you can add complications like shortening the table name separately from the column names or actually checking for uniqueness.

Note, this is from a Rails 5.2 project; if you decide to do this, copy the source from your version.

Answer 8

I had this issue, but with the timestamps function. It was autogenerating an index on updated_at that exceeded the 63 character limit:

def change
  create_table :toooooooooo_loooooooooooooooooooooooooooooong do |t|
    t.timestamps
  end
end

Index name 'index_toooooooooo_loooooooooooooooooooooooooooooong_on_updated_at' on table 'toooooooooo_loooooooooooooooooooooooooooooong' is too long; the limit is 63 characters

I tried to use timestamps to specify the index name:

def change
  create_table :toooooooooo_loooooooooooooooooooooooooooooong do |t|
    t.timestamps index: { name: 'too_loooooooooooooooooooooooooooooong_updated_at' }
  end
end

However, this tries to apply the index name to both the updated_at and created_at fields:

Index name 'too_long_updated_at' on table 'toooooooooo_loooooooooooooooooooooooooooooong' already exists

Finally I gave up on timestamps and just created the timestamps the long way:

def change
  create_table :toooooooooo_loooooooooooooooooooooooooooooong do |t|
    t.datetime :updated_at, index: { name: 'too_long_on_updated_at' }
    t.datetime :created_at, index: { name: 'too_long_on_created_at' }
  end
end

This works but I'd love to hear if it's possible with the timestamps method!

Answer 9

create_table :you_table_name do |t| t.references :studant, index: { name: 'name_for_studant_index' } t.references :teacher, index: { name: 'name_for_teacher_index' } end