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

Question

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   gt  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

User · Answer

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   gt   index my shorter name   end

User · Answer

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

User · Answer

You can also do  t index   branch id   party id   unique  true  name   by branch party     as in the Ruby on Rails API

User · Answer

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

User · Answer

Provide the  name option to add index  e g   add index  studies      quot user id quot    quot university id quot    quot subject name id quot    quot subject type id quot       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

User · Answer

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

User · Answer

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

User · Answer

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 amp A that helped me out  https   stackoverflow com a 30366460 3258059

User · Answer

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

[ruby-on-rails] How do I handle too long index names in a Ruby on Rails ActiveRecord migration?

Examples related to ruby-on-rails

Examples related to migration