如何在 Ruby on Rails ActiveRecord 迁移中处理过长的索引名称？

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

提问by JJD

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

表 'studies' 上的索引名称 'index_studies_on_user_id_and_university_id_and_subject_name_id_and_subject_type_id' 太长；限制为 64 个字符

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

我该如何处理？我可以指定不同的索引名称吗？

回答by fl00r

Provide the :nameoption to add_index, e.g.:

提供:name选项add_index，例如：

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

If using the :indexoption on referencesin a create_tableblock, it takes the same options hash as add_indexas its value:

如果在块中使用:indexon 选项，它将采用相同的选项哈希作为其值：referencescreate_tableadd_index

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

回答by Craig Walker

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

您还可以更改create_table块内列定义中的索引名称（例如从迁移生成器获取）。

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

回答by ecoologic

In PostgreSQL, the defaultlimit 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):

在PostgreSQL，在默认情况下限制为63个字符。因为索引名称必须是唯一的，所以有一点约定是很好的。我使用（我调整了示例以解释更复杂的结构）：

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:

逻辑是：

• indexbecomes idx
• Singular table name
• No joining words
• No _id
• Alphabetical order

• index变成 idx
• 单表名
• 没有连接词
• 不 _id
• 按字母顺序

Which usually does the job.

这通常可以完成这项工作。

回答by tomascharad

You can also do

你也可以这样做

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

as in the Ruby on Rails API.

就像在Ruby on Rails API 中一样。

回答by Nadeem Yasin

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

与上一个答案类似：只需将 'name' 键与常规 add_index 行一起使用：

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

回答by whatapalaver

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

恐怕这些解决方案都不适合我。也许是因为我belongs_to在 create_table 迁移中使用了多态关联。

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

这是帮助我的 SO Q&A：https: //stackoverflow.com/a/30366460/3258059

回答by Fred Willmore

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

我有这个问题，但timestamps功能。它在 updated_at 上自动生成一个超过 63 个字符限制的索引：

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

表 'tooooooooooo_looooooooooooooooooooooooooooong' 上的索引名称 'index_tooooooooooo_looooooooooooooooooooooooooooooong_on_updated_at' 太长；限制为 63 个字符

I tried to use timestampsto specify the index name:

我试图用来timestamps指定索引名称：

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_atand created_atfields:

但是，这会尝试将索引名称应用于updated_at和created_at字段：

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

表 'toooooooooo_looooooooooooooooooooooooooooooong' 上的索引名称 'too_long_updated_at' 已经存在

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

最后我放弃了timestamps，只是创建了很长的时间戳：

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 timestampsmethod!

这有效，但我很想知道该timestamps方法是否可行！

回答by 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

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