ChatGPT解决这个技术问题 Extra ChatGPT

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

I am trying to add an 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?


R
Ri1a

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 }

According to APIdock the name has to be a string, not a symbol
The down syntax for this is remove_index :studies, :name => 'my_index' for anyone who needs it
On Rails 4.2.6 works with index: { name: :my_index }. Only name did not work.
C
Craig Walker

You can also 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

Note that this doesn't create the multi-column index from the original question; it's just demonstrating how to shorten a long index name from a create_table
This helped me when I was trying to create a polymorphic reference on a name spaced table with an index t.references :searchable, polymorphic:true, index: {:name => "index_searches_on_searchable"} in this case the index was in fact a multi-column(searchable_id and searchable_type) and the addition of the namespace in the generated name became very long.
nice, compact solution for me. Thank you!
Should also have foreign_key: true and by the way, this is a great solution since it's the easiest to use when you have a migration file created with the rails generator model:references format
P
Peter Mortensen

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.


Thanks for sharing. Would be nice if you could link the Postgres documentation for the limitation fact.
Do we need the table name in the index name since the index belongs to that table anyway? Just curious if that's beneficial somewhere I haven't seen.
You can name the index what you want, but I think the table name in the index name helps to keep the index name unique (which is mandatory), well scoped and improves some error message readability.
P
Peter Mortensen

You can also do

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

as in the Ruby on Rails API.


P
Peter Mortensen

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

w
whatapalaver

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


J
Jerph

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.


F
Fred Willmore

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!


A
Adário Muatelembe

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